Excel question

Okay . . . let's try this again.

Moderators: Shirley, brian, ZMan, Sabo

Post Reply
Gunpowder
The Dude
Posts: 6591
Joined: Mon Mar 11, 2013 7:52 am
Location: NAPES, FLURRDA
Contact:

Excel question

Post by Gunpowder » Sun Apr 19, 2020 11:36 am

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?
"Enjoyed this one," Gunpowder told the Burlington Observer. "But it's back to work tomorrow."

Gunpowder
The Dude
Posts: 6591
Joined: Mon Mar 11, 2013 7:52 am
Location: NAPES, FLURRDA
Contact:

Re: Excel question

Post by Gunpowder » Sun Apr 19, 2020 11:39 am

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.
"Enjoyed this one," Gunpowder told the Burlington Observer. "But it's back to work tomorrow."

Gunpowder
The Dude
Posts: 6591
Joined: Mon Mar 11, 2013 7:52 am
Location: NAPES, FLURRDA
Contact:

Re: Excel question

Post by Gunpowder » Sun Apr 19, 2020 11:59 am

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?
"Enjoyed this one," Gunpowder told the Burlington Observer. "But it's back to work tomorrow."

User avatar
A_B
The Dude
Posts: 16131
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 » Sun Apr 19, 2020 12:01 pm

Hmmm. Probably needs a Couple of VLOOKUP columns for each value? I’m not 100 percent sure I know what you are asking
The wife just accidentally called you. “Accidentally”. Sorry

Gunpowder
The Dude
Posts: 6591
Joined: Mon Mar 11, 2013 7:52 am
Location: NAPES, FLURRDA
Contact:

Re: Excel question

Post by Gunpowder » Sun Apr 19, 2020 12:12 pm

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.
"Enjoyed this one," Gunpowder told the Burlington Observer. "But it's back to work tomorrow."

User avatar
A_B
The Dude
Posts: 16131
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 » Thu May 21, 2020 9:33 am

Not a question, but I get to use Vlookup for the first time in a while. Kinda excited.
The wife just accidentally called you. “Accidentally”. Sorry

User avatar
A_B
The Dude
Posts: 16131
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 » Thu May 21, 2020 12:17 pm

Update: It was fun.
The wife just accidentally called you. “Accidentally”. Sorry

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

Re: Excel question

Post by wlu_lax6 » 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)

teeteebee
Brandt
Posts: 317
Joined: Sun Mar 17, 2013 6:50 am

Re: Excel question

Post by teeteebee » 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...
oh shit...

User avatar
Square Rob
Brandt
Posts: 410
Joined: Thu Mar 14, 2013 3:43 pm

Re: Excel question

Post by Square Rob » Fri May 22, 2020 4:10 am

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: 6591
Joined: Mon Mar 11, 2013 7:52 am
Location: NAPES, FLURRDA
Contact:

Re: Excel question

Post by Gunpowder » 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.
"Enjoyed this one," Gunpowder told the Burlington Observer. "But it's back to work tomorrow."

User avatar
A_B
The Dude
Posts: 16131
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 » Fri May 22, 2020 7:59 am

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.
The wife just accidentally called you. “Accidentally”. Sorry

User avatar
govmentchedda
The Dude
Posts: 7831
Joined: Mon Mar 11, 2013 4:36 pm

Re: Excel question

Post by govmentchedda » Sat Jul 11, 2020 8:59 am

I took a two hour nap and cried during parks and Rec. it was awesome and terrifying.

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

Re: Excel question

Post by wlu_lax6 » Thu Aug 06, 2020 11:20 pm


User avatar
bfj
Donny
Posts: 2681
Joined: Mon Apr 01, 2013 11:08 pm

Re: Excel question

Post by bfj » Thu Aug 13, 2020 11:36 am

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?
Enjoy every sandwich.

User avatar
mister d
The Dude
Posts: 19400
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel question

Post by mister d » 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.)
rass wrote:
Mon Apr 20, 2020 11:20 am
I'm definitely known as "the boy who cried penis" in town.

User avatar
Giff
The Dude
Posts: 6364
Joined: Mon Mar 25, 2013 3:26 pm

Re: Excel question

Post by Giff » 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?
Muh.

User avatar
bfj
Donny
Posts: 2681
Joined: Mon Apr 01, 2013 11:08 pm

Re: Excel question

Post by bfj » 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.
Enjoy every sandwich.

User avatar
bfj
Donny
Posts: 2681
Joined: Mon Apr 01, 2013 11:08 pm

Re: Excel question

Post by bfj » 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!
Enjoy every sandwich.

duff
The Big Lebowski
Posts: 1930
Joined: Mon Apr 01, 2013 3:36 pm

Re: Excel question

Post by duff » 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.
To quote both Bruce Prichard and Tony Schiavone, "Fuck Duff Meltzer."

User avatar
A_B
The Dude
Posts: 16131
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 » Thu Aug 13, 2020 11:59 am

but the remove duplicates would still work in that instance, right?
The wife just accidentally called you. “Accidentally”. Sorry

User avatar
mister d
The Dude
Posts: 19400
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel question

Post by mister d » Thu Aug 13, 2020 12:00 pm

Single cell would work with what I said too, with the same "if any character is different it will read them as different" caveat.
rass wrote:
Mon Apr 20, 2020 11:20 am
I'm definitely known as "the boy who cried penis" in town.

User avatar
mister d
The Dude
Posts: 19400
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel question

Post by mister d » Thu Aug 13, 2020 12:01 pm

image.png
image.png (79.8 KiB) Viewed 178 times
rass wrote:
Mon Apr 20, 2020 11:20 am
I'm definitely known as "the boy who cried penis" in town.

User avatar
Giff
The Dude
Posts: 6364
Joined: Mon Mar 25, 2013 3:26 pm

Re: Excel question

Post by Giff » Thu Aug 13, 2020 12:04 pm

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.
Muh.

User avatar
bfj
Donny
Posts: 2681
Joined: Mon Apr 01, 2013 11:08 pm

Re: Excel question

Post by bfj » Thu Aug 13, 2020 12:12 pm

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
Enjoy every sandwich.

User avatar
sancarlos
The Dude
Posts: 11631
Joined: Fri Mar 15, 2013 1:46 pm
Location: NorCal via Colorado

Re: Excel question

Post by sancarlos » Thu Aug 13, 2020 8:03 pm

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

I never shoulda punched that horse. - AB

User avatar
mister d
The Dude
Posts: 19400
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel question

Post by mister d » Thu Aug 13, 2020 8:07 pm

=if(D>9”,”Yes”,”No”)
rass wrote:
Mon Apr 20, 2020 11:20 am
I'm definitely known as "the boy who cried penis" in town.

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

Re: Excel question

Post by wlu_lax6 » Wed Aug 19, 2020 3:40 pm


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

Re: Excel question

Post by wlu_lax6 » Mon Sep 14, 2020 2:00 pm

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: 19400
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel question

Post by mister d » 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.))
rass wrote:
Mon Apr 20, 2020 11:20 am
I'm definitely known as "the boy who cried penis" in town.

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

Re: Excel question

Post by wlu_lax6 » Mon Sep 14, 2020 4:05 pm

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: 19400
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel question

Post by mister d » Mon Sep 14, 2020 4:25 pm

Can I ask what this could possibly be for?
rass wrote:
Mon Apr 20, 2020 11:20 am
I'm definitely known as "the boy who cried penis" in town.

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

Re: Excel question

Post by wlu_lax6 » Mon Sep 14, 2020 9:12 pm

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.

Post Reply