Excel question

Okay . . . let's try this again.

Moderators: Shirley, Sabo, brian, rass, DaveInSeattle

Gunpowder
The Dude
Posts: 8525
Joined: Mon Mar 11, 2013 7:52 am
Location: Dipshitville, FL
Contact:

Excel question

Post by Gunpowder »

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?
Pack a vest for your james in the city of intercourse
Gunpowder
The Dude
Posts: 8525
Joined: Mon Mar 11, 2013 7:52 am
Location: Dipshitville, FL
Contact:

Re: Excel question

Post by Gunpowder »

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.
Pack a vest for your james in the city of intercourse
Gunpowder
The Dude
Posts: 8525
Joined: Mon Mar 11, 2013 7:52 am
Location: Dipshitville, FL
Contact:

Re: Excel question

Post by Gunpowder »

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
User avatar
A_B
The Dude
Posts: 23319
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel question

Post by A_B »

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.
Gunpowder
The Dude
Posts: 8525
Joined: Mon Mar 11, 2013 7:52 am
Location: Dipshitville, FL
Contact:

Re: Excel question

Post by Gunpowder »

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.
Pack a vest for your james in the city of intercourse
User avatar
A_B
The Dude
Posts: 23319
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel question

Post by A_B »

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.
User avatar
A_B
The Dude
Posts: 23319
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel question

Post by A_B »

Update: It was fun.
You know what you need? A lyrical sucker punch to the face.
User avatar
wlu_lax6
The Dude
Posts: 10399
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel question

Post by wlu_lax6 »

A_B wrote: Thu May 21, 2020 12:17 pm Update: It was fun.
Just wait to you try Hlookup or the joy of using indexmatch instead of vlookup (Lookup to the left and ability to separate lookup and return columns)
teeteebee
Brandt
Posts: 366
Joined: Sun Mar 17, 2013 6:50 am

Re: Excel question

Post by teeteebee »

wlu_lax6 wrote: Thu May 21, 2020 2:42 pm
A_B wrote: Thu May 21, 2020 12:17 pm Update: It was fun.
Just wait to you try Hlookup or the joy of using indexmatch instead of vlookup (Lookup to the left and ability to separate lookup and return columns)
Still waiting to gain access to XLOOKUP...
oh shit...
User avatar
Square Rob
Jesus Quintana
Posts: 769
Joined: Thu Mar 14, 2013 3:43 pm

Re: Excel question

Post by Square Rob »

wlu_lax6 wrote: Thu May 21, 2020 2:42 pm
A_B wrote: Thu May 21, 2020 12:17 pm Update: It was fun.
Just wait to you try Hlookup or the joy of using indexmatch instead of vlookup (Lookup to the left and ability to separate lookup and return columns)
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.
Gunpowder
The Dude
Posts: 8525
Joined: Mon Mar 11, 2013 7:52 am
Location: Dipshitville, FL
Contact:

Re: Excel question

Post by Gunpowder »

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
User avatar
A_B
The Dude
Posts: 23319
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel question

Post by A_B »

Gunpowder wrote: Fri May 22, 2020 6:18 am 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.
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.
User avatar
govmentchedda
The Dude
Posts: 12672
Joined: Mon Mar 11, 2013 4:36 pm

Re: Excel question

Post by govmentchedda »

Until everything is less insane, I'm mixing weed with wine.
User avatar
wlu_lax6
The Dude
Posts: 10399
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel question

Post by wlu_lax6 »

User avatar
bfj
Walter Sobchak
Posts: 3960
Joined: Mon Apr 01, 2013 11:08 pm

Re: Excel question

Post by bfj »

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?
BFJ is the town wizard who runs a magic shop. He also has a golem that he has trained to attack anti-Semites.
User avatar
mister d
The Dude
Posts: 29045
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel question

Post by mister d »

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.)
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
Giff
The Dude
Posts: 10794
Joined: Mon Mar 25, 2013 3:26 pm

Re: Excel question

Post by Giff »

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
User avatar
bfj
Walter Sobchak
Posts: 3960
Joined: Mon Apr 01, 2013 11:08 pm

Re: Excel question

Post by bfj »

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.)
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.
BFJ is the town wizard who runs a magic shop. He also has a golem that he has trained to attack anti-Semites.
User avatar
bfj
Walter Sobchak
Posts: 3960
Joined: Mon Apr 01, 2013 11:08 pm

Re: Excel question

Post by bfj »

Giff wrote: Thu Aug 13, 2020 11:47 am I suck at Excel and people will probably laugh at this suggestion, but what about a pivot table?
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.
User avatar
duff
Donny
Posts: 2745
Joined: Mon Apr 01, 2013 3:36 pm

Re: Excel question

Post by duff »

bfj wrote: Thu Aug 13, 2020 11:57 am
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.)
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.
The same cell? Might as well have written it in Notepad.
To quote both Bruce Prichard and Tony Schiavone, "Fuck Duff Meltzer."
User avatar
A_B
The Dude
Posts: 23319
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel question

Post by A_B »

but the remove duplicates would still work in that instance, right?
You know what you need? A lyrical sucker punch to the face.
User avatar
mister d
The Dude
Posts: 29045
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel question

Post by mister d »

Single cell would work with what I said too, with the same "if any character is different it will read them as different" caveat.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
mister d
The Dude
Posts: 29045
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel question

Post by mister d »

image.png
image.png (79.8 KiB) Viewed 771 times
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
Giff
The Dude
Posts: 10794
Joined: Mon Mar 25, 2013 3:26 pm

Re: Excel question

Post by Giff »

bfj wrote: Thu Aug 13, 2020 11:57 am
Giff wrote: Thu Aug 13, 2020 11:47 am I suck at Excel and people will probably laugh at this suggestion, but what about a pivot table?
I suck at Excel also and I'm not even sure I know what a pivot table is!
It's the Excel version of Clippy, but it's David Schwimmer.
well this is gonna be someone's new signature - bronto
User avatar
bfj
Walter Sobchak
Posts: 3960
Joined: Mon Apr 01, 2013 11:08 pm

Re: Excel question

Post by bfj »

duff wrote: Thu Aug 13, 2020 11:58 am
bfj wrote: Thu Aug 13, 2020 11:57 am
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.)
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.
The same cell? Might as well have written it in Notepad.
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.

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.
User avatar
sancarlos
The Dude
Posts: 18060
Joined: Fri Mar 15, 2013 1:46 pm
Location: NorCal via Colorado

Re: Excel question

Post by sancarlos »

teeteebee wrote: Thu May 21, 2020 6:00 pm
wlu_lax6 wrote: Thu May 21, 2020 2:42 pm
A_B wrote: Thu May 21, 2020 12:17 pm Update: It was fun.
Just wait to you try Hlookup or the joy of using indexmatch instead of vlookup (Lookup to the left and ability to separate lookup and return columns)
Still waiting to gain access to XLOOKUP...
Not as fun as XL Hookup.
"What a bunch of pedantic pricks." - sybian
User avatar
mister d
The Dude
Posts: 29045
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel question

Post by mister d »

=if(D>9”,”Yes”,”No”)
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
wlu_lax6
The Dude
Posts: 10399
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel question

Post by wlu_lax6 »

User avatar
wlu_lax6
The Dude
Posts: 10399
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel question

Post by wlu_lax6 »

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
User avatar
mister d
The Dude
Posts: 29045
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel question

Post by mister d »

=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.))
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
wlu_lax6
The Dude
Posts: 10399
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel question

Post by wlu_lax6 »

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.))
Ah the add the year remove the year move...Did not think about that manipulation.
User avatar
mister d
The Dude
Posts: 29045
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel question

Post by mister d »

Can I ask what this could possibly be for?
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
wlu_lax6
The Dude
Posts: 10399
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel question

Post by wlu_lax6 »

mister d wrote: Mon Sep 14, 2020 4:25 pm Can I ask what this could possibly be for?
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.
User avatar
govmentchedda
The Dude
Posts: 12672
Joined: Mon Mar 11, 2013 4:36 pm

Re: Excel question

Post by govmentchedda »

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?
Until everything is less insane, I'm mixing weed with wine.
P.D.X.
The Dude
Posts: 5280
Joined: Wed Mar 13, 2013 12:31 pm

Re: Excel question

Post by P.D.X. »

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?
Should automatically add new cells to the function if they're added between other cells of the same function. (At least gsheets does this)
User avatar
A_B
The Dude
Posts: 23319
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel question

Post by A_B »

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?
Will you please have the lawyers step away from excel. Back away slowly. And hire Mister D to fix this for you.
You know what you need? A lyrical sucker punch to the face.
User avatar
mister d
The Dude
Posts: 29045
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel question

Post by mister d »

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.)
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
A_B
The Dude
Posts: 23319
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel question

Post by A_B »

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.)
Nvm. This way is better. Just send that exact quote from Mr d to whoever is creating. And send me the response.
You know what you need? A lyrical sucker punch to the face.
User avatar
Steve of phpBB
The Dude
Posts: 8434
Joined: Mon Mar 11, 2013 10:44 am
Location: Feeling gravity's pull

Re: Excel question

Post by Steve of phpBB »

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.
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.
User avatar
mister d
The Dude
Posts: 29045
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel question

Post by mister d »

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.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
Post Reply