Excel question
Moderators: Shirley, Sabo, brian, rass, DaveInSeattle
Excel question
Yo Excel Wizards and Jerloma,
Is there a formula that will pull row and column values from an entire array? MATCH appears to only search one column or one row.
I have a table that has values from 1 thru whatever (in increments of one) and need to pull the values from the row and column (ie value 1 is at x=2500, y=3000 or something like that, I need to search for "1" in the table and get 2500 and 3000). If I can get the row and column numbers I can use another formula to do this, but right now I'm stuck. Anyone got a solution?
Is there a formula that will pull row and column values from an entire array? MATCH appears to only search one column or one row.
I have a table that has values from 1 thru whatever (in increments of one) and need to pull the values from the row and column (ie value 1 is at x=2500, y=3000 or something like that, I need to search for "1" in the table and get 2500 and 3000). If I can get the row and column numbers I can use another formula to do this, but right now I'm stuck. Anyone got a solution?
Pack a vest for your james in the city of intercourse
Re: Excel question
Wait I could maybe use Index for this and just make it messy as hell, no?
EDIT: NM, Index just gives the value at the row and column. I need that in reverse.
EDIT: NM, Index just gives the value at the row and column. I need that in reverse.
Pack a vest for your james in the city of intercourse
Re: Excel question
Internet's best solution is to multiply it by arrays containing the values of the rows and columns using SUMPRODUCT...is that my best bet?
Pack a vest for your james in the city of intercourse
- A_B
- The Dude
- Posts: 23428
- Joined: Mon Mar 11, 2013 7:36 am
- Location: Getting them boards like a wolf in the chicken pen.
Re: Excel question
Hmmm. Probably needs a Couple of VLOOKUP columns for each value? I’m not 100 percent sure I know what you are asking
You know what you need? A lyrical sucker punch to the face.
Re: Excel question
SUMPRODUCT worked....I am searching a matrix for option plays I want to look at that meet my criteria and each match gets a value (ie the first match gets a 1, second gets a 2, etc. etc.). So to lay it out for me to look at in the terms I need to look at it in, I need to get "2630" (row) and "2580" (column), which signifies that I want to look at buying a 2580 option and selling a 2630 option for Amazon in this case. With these values, then I do the VLOOKUP.
I made two corresponding arrays that have the row numbers and column numbers, ie:
1 2 3 4 5 6 7
11 2 3 4 5 6 7
21 2 3 4 5 6 7
31 2 3 4 5 6 7
41 2 3 4 5 6 7
51 2 3 4 5 6 7
61 2 3 4 5 6 7
71 2 3 4 5 6 7
and
1 2 3 4 5 6 7
1 1 1 1 1 1 1 1
2 2 2 2 2 2 2 2
3 3 3 3 33 3 3 3
4 4 4 4 44 4 4
etc etc.
and multiplied the arrays with SUMPRODUCT. So the first cell matches "1" in my results array and multiplies an array full of zeros with a single one against the row array or the column array to get the value. It worked so it's kinda messy but fine for this usage.
I made two corresponding arrays that have the row numbers and column numbers, ie:
1 2 3 4 5 6 7
11 2 3 4 5 6 7
21 2 3 4 5 6 7
31 2 3 4 5 6 7
41 2 3 4 5 6 7
51 2 3 4 5 6 7
61 2 3 4 5 6 7
71 2 3 4 5 6 7
and
1 2 3 4 5 6 7
1 1 1 1 1 1 1 1
2 2 2 2 2 2 2 2
3 3 3 3 33 3 3 3
4 4 4 4 44 4 4
etc etc.
and multiplied the arrays with SUMPRODUCT. So the first cell matches "1" in my results array and multiplies an array full of zeros with a single one against the row array or the column array to get the value. It worked so it's kinda messy but fine for this usage.
Pack a vest for your james in the city of intercourse
- A_B
- The Dude
- Posts: 23428
- Joined: Mon Mar 11, 2013 7:36 am
- Location: Getting them boards like a wolf in the chicken pen.
Re: Excel question
Not a question, but I get to use Vlookup for the first time in a while. Kinda excited.
You know what you need? A lyrical sucker punch to the face.
- Square Rob
- Jesus Quintana
- Posts: 769
- Joined: Thu Mar 14, 2013 3:43 pm
Re: Excel question
I use indexmatch strings all the time and love it. Best thing I like about it is you can easily include other functions inside the match function. I’ve built a number of complex daily reports that need to pareto an ever changing list of items and display them mtd. Using the rank function and then an index match I can pull out the top ten each day automatically and even give it functionality where you can select any day you want to see what it looked like that day. Almost impossible to do with just vlookups.
Re: Excel question
I'm going to have to look into this - there are so many examples where I just spend a full sheet rearranging items so VLOOKUP can find them.
Pack a vest for your james in the city of intercourse
- A_B
- The Dude
- Posts: 23428
- Joined: Mon Mar 11, 2013 7:36 am
- Location: Getting them boards like a wolf in the chicken pen.
Re: Excel question
Yeah, my data was already formatted in a similar format so vlookup was no issue, but Indexmatch does look to have a lot of utility.
You know what you need? A lyrical sucker punch to the face.
- govmentchedda
- The Dude
- Posts: 12751
- Joined: Mon Mar 11, 2013 4:36 pm
Re: Excel question
Until everything is less insane, I'm mixing weed with wine.
Re: Excel question
Here is my Excel question. The org I work for wants to do a mass mailing to our 23k person database. That's easy. What isn't easy is they don't want 5 mailers going to the same address. We have some accounts in there with husband, wife, kids, etc. because our system was used to register people for our 5k runs for many years.
I don't want to delete duplicates because I want at least one mailer to go to each address. Is there a way for the system to find the duplicates and show them to me so I can delete the ones I want to delete?
Does that make any sense?
I don't want to delete duplicates because I want at least one mailer to go to each address. Is there a way for the system to find the duplicates and show them to me so I can delete the ones I want to delete?
Does that make any sense?
BFJ is the town wizard who runs a magic shop. He also has a golem that he has trained to attack anti-Semites.
Re: Excel question
As long as you have consistent formatting, yes. If its column A street, column B city ... you just select the entire list across all columns and rows and then go to the data tab -> remove duplicates. I'd recommend doing this in a copy file just in case you mess it up the first time.
(This won't catch differences like "New Jersey" and "NJ" but if everyone has the exact same address, it would just remove the extra row(s) keeping just one.)
(This won't catch differences like "New Jersey" and "NJ" but if everyone has the exact same address, it would just remove the extra row(s) keeping just one.)
Re: Excel question
I suck at Excel and people will probably laugh at this suggestion, but what about a pivot table?
well this is gonna be someone's new signature - bronto
Re: Excel question
That would work, but all address info is in the same cell, address, street, city, state, zip. I'm going to see if I can fix it to put all of that in separate columns. Thanks.mister d wrote: ↑Thu Aug 13, 2020 11:46 am As long as you have consistent formatting, yes. If its column A street, column B city ... you just select the entire list across all columns and rows and then go to the data tab -> remove duplicates. I'd recommend doing this in a copy file just in case you mess it up the first time.
(This won't catch differences like "New Jersey" and "NJ" but if everyone has the exact same address, it would just remove the extra row(s) keeping just one.)
BFJ is the town wizard who runs a magic shop. He also has a golem that he has trained to attack anti-Semites.
Re: Excel question
I suck at Excel also and I'm not even sure I know what a pivot table is!
BFJ is the town wizard who runs a magic shop. He also has a golem that he has trained to attack anti-Semites.
Re: Excel question
The same cell? Might as well have written it in Notepad.bfj wrote: ↑Thu Aug 13, 2020 11:57 amThat would work, but all address info is in the same cell, address, street, city, state, zip. I'm going to see if I can fix it to put all of that in separate columns. Thanks.mister d wrote: ↑Thu Aug 13, 2020 11:46 am As long as you have consistent formatting, yes. If its column A street, column B city ... you just select the entire list across all columns and rows and then go to the data tab -> remove duplicates. I'd recommend doing this in a copy file just in case you mess it up the first time.
(This won't catch differences like "New Jersey" and "NJ" but if everyone has the exact same address, it would just remove the extra row(s) keeping just one.)
To quote both Bruce Prichard and Tony Schiavone, "Fuck Duff Meltzer."
- A_B
- The Dude
- Posts: 23428
- Joined: Mon Mar 11, 2013 7:36 am
- Location: Getting them boards like a wolf in the chicken pen.
Re: Excel question
but the remove duplicates would still work in that instance, right?
You know what you need? A lyrical sucker punch to the face.
Re: Excel question
Single cell would work with what I said too, with the same "if any character is different it will read them as different" caveat.
Re: Excel question
It's the Excel version of Clippy, but it's David Schwimmer.
well this is gonna be someone's new signature - bronto
Re: Excel question
I realized that the way I wrote the query from our client software was giving me the address in one cell. I was able to split it out into different columns and now I can use conditional formatting to find the same addresses. I don't want to use the client names bc we may have 20 people who all used their work address as a part of a race team. But if I just use street address I can see all the duplicates and then go through them and find what I need.duff wrote: ↑Thu Aug 13, 2020 11:58 amThe same cell? Might as well have written it in Notepad.bfj wrote: ↑Thu Aug 13, 2020 11:57 amThat would work, but all address info is in the same cell, address, street, city, state, zip. I'm going to see if I can fix it to put all of that in separate columns. Thanks.mister d wrote: ↑Thu Aug 13, 2020 11:46 am As long as you have consistent formatting, yes. If its column A street, column B city ... you just select the entire list across all columns and rows and then go to the data tab -> remove duplicates. I'd recommend doing this in a copy file just in case you mess it up the first time.
(This won't catch differences like "New Jersey" and "NJ" but if everyone has the exact same address, it would just remove the extra row(s) keeping just one.)
Thanks for saving me a shit load of time for a project I may or may not have to do.
TWILTS
BFJ is the town wizard who runs a magic shop. He also has a golem that he has trained to attack anti-Semites.
Re: Excel question
Not as fun as XL Hookup.
"What a bunch of pedantic pricks." - sybian
Re: Excel question
Microsoft 1- Human Genome 0
https://www-theverge-com.cdn.ampproject ... ding-dates
https://www-theverge-com.cdn.ampproject ... ding-dates
Re: Excel question
Have a date with a random year, example 6/6/1995. I want a formula that returns just month and day so I can then calculate a date 119 days later.
So given 6/6/1995 the answer I want is 10/4/2020
and given 6/5/1973 the answer I ultimately want is 10/3/2020
So given 6/6/1995 the answer I want is 10/4/2020
and given 6/5/1973 the answer I ultimately want is 10/3/2020
Re: Excel question
=DATE(YEAR(A1)+2020-YEAR(A1),MONTH(A1),DAY(A1)+119)
(I'm not getting the same answers as you for +119. If you want yours, I guess its the same formula +120.)
((Knowing this means anything before March 1st would potentially return different dates depending on whether its a leap year.))
(I'm not getting the same answers as you for +119. If you want yours, I guess its the same formula +120.)
((Knowing this means anything before March 1st would potentially return different dates depending on whether its a leap year.))
Re: Excel question
Ah the add the year remove the year move...Did not think about that manipulation.mister d wrote: ↑Mon Sep 14, 2020 2:14 pm =DATE(YEAR(A1)+2020-YEAR(A1),MONTH(A1),DAY(A1)+119)
(I'm not getting the same answers as you for +119. If you want yours, I guess its the same formula +120.)
((Knowing this means anything before March 1st would potentially return different dates depending on whether its a leap year.))
Re: Excel question
My company has required continuing education for the firms that are in our industry. We have a fancy new reporting tool we exposed to the staff at these organizations that let them build their own reports and such. However, as soon as someone complete the CE the values for the window in which they have to complete their CE goes to "null". However, we do make available their base date that the CE window start date is calculated from (have to take CE 2 years after you start and then every 3 years after that and you get a 120 day window to complete it). One firm has an analysis they do to figure out how many days before the deadline people complete their CE requirement. Was thinking about a way this firm could do it their self after exporting almost the perfect report.
This would not be necessary if the person's full CE history was passed to the fancy reporting tool.
- govmentchedda
- The Dude
- Posts: 12751
- Joined: Mon Mar 11, 2013 4:36 pm
Re: Excel question
A group of family lawyers is trying to come up with a spreadsheet to use in standard divorce cases. It's really a basic spreadsheet, some addition, some subtraction, a minor bit of division (getting parties to 50% each of assets and liabilities), and they presented it last night as a .pdf. The problem with it as a .pdf (beyond only being able to handwrite on it), is that they only had 2 or 3 rows for a specific type of asset (i.e. 3 entries for bank account). Is there a way to make an Excel form(template?) where you can add rows, and the cells for summary or total remain unchanged?
Maybe the other alternative way to say this would be to say is there a way to have a spreadsheet that would have 20 rows, but if you only enter information on 3 of them, the rest of the blank rows would automatically go away in a certain view?
Maybe the other alternative way to say this would be to say is there a way to have a spreadsheet that would have 20 rows, but if you only enter information on 3 of them, the rest of the blank rows would automatically go away in a certain view?
Until everything is less insane, I'm mixing weed with wine.
Re: Excel question
Should automatically add new cells to the function if they're added between other cells of the same function. (At least gsheets does this)govmentchedda wrote: ↑Thu Feb 04, 2021 4:55 pm Is there a way to make an Excel form(template?) where you can add rows, and the cells for summary or total remain unchanged?
- A_B
- The Dude
- Posts: 23428
- Joined: Mon Mar 11, 2013 7:36 am
- Location: Getting them boards like a wolf in the chicken pen.
Re: Excel question
Will you please have the lawyers step away from excel. Back away slowly. And hire Mister D to fix this for you.govmentchedda wrote: ↑Thu Feb 04, 2021 4:55 pm A group of family lawyers is trying to come up with a spreadsheet to use in standard divorce cases. It's really a basic spreadsheet, some addition, some subtraction, a minor bit of division (getting parties to 50% each of assets and liabilities), and they presented it last night as a .pdf. The problem with it as a .pdf (beyond only being able to handwrite on it), is that they only had 2 or 3 rows for a specific type of asset (i.e. 3 entries for bank account). Is there a way to make an Excel form(template?) where you can add rows, and the cells for summary or total remain unchanged?
Maybe the other alternative way to say this would be to say is there a way to have a spreadsheet that would have 20 rows, but if you only enter information on 3 of them, the rest of the blank rows would automatically go away in a certain view?
You know what you need? A lyrical sucker punch to the face.
Re: Excel question
If I were setting up this form, I'd probably just create a lot of bank account rows then a macro behind it to hide the blanks before you PDF.
(Or, if you don't need the detail on the final product, just a separate input tab that feeds a single "bank accounts" row on the main form.)
(Or, if you don't need the detail on the final product, just a separate input tab that feeds a single "bank accounts" row on the main form.)
- A_B
- The Dude
- Posts: 23428
- Joined: Mon Mar 11, 2013 7:36 am
- Location: Getting them boards like a wolf in the chicken pen.
Re: Excel question
Nvm. This way is better. Just send that exact quote from Mr d to whoever is creating. And send me the response.mister d wrote: ↑Thu Feb 04, 2021 5:30 pm If I were setting up this form, I'd probably just create a lot of bank account rows then a macro behind it to hide the blanks before you PDF.
(Or, if you don't need the detail on the final product, just a separate input tab that feeds a single "bank accounts" row on the main form.)
You know what you need? A lyrical sucker punch to the face.
- Steve of phpBB
- The Dude
- Posts: 8507
- Joined: Mon Mar 11, 2013 10:44 am
- Location: Feeling gravity's pull
Re: Excel question
That sounds really useful - how generally does one do that? (Is that the kind of thing that can be briefly explained without having to go into huge detail?)
And his one problem is he didn’t go to Russia that night because he had extracurricular activities, and they froze to death.
Re: Excel question
There are fancier ways but the quickest is have A and B outside the print range, where A is the row number and B is A + 100 if the bank account dollar cell is blank, then the macro can sort it below the print range.