MMDCisco
asked on
No Macros/vba: Offset Manipulation for data retrieval, 2 columns
I have two columns:
Item Color
shirt red
pants red
shirt blue
pants green
about 3000 rows
I'd like to add a third column that looks like this:
shirt red | shirts, pants
pants red | shirts, pants
shirts blue
pants green
goes on and on.
The first option to get the first red item is easy:
Assuming red is in G13 and the list starts in G12: =OFFSET(G14,MATCH(G14,$G$1
Finding populating the next items has proven more difficult.
I cannot use a macro for this, please take this into consideration. Also, arrays prove to be unwieldy for the reporting tool during presentation.
Please advise & Happy New Year!
Item Color
shirt red
pants red
shirt blue
pants green
about 3000 rows
I'd like to add a third column that looks like this:
shirt red | shirts, pants
pants red | shirts, pants
shirts blue
pants green
goes on and on.
The first option to get the first red item is easy:
Assuming red is in G13 and the list starts in G12: =OFFSET(G14,MATCH(G14,$G$1
Finding populating the next items has proven more difficult.
I cannot use a macro for this, please take this into consideration. Also, arrays prove to be unwieldy for the reporting tool during presentation.
Please advise & Happy New Year!
Homework?
ASKER
No, not homework. Just an interesting problem I ran into last night when I came across a lookup issue for a small project. I'm too old for homework.
eeclothes.xls
eeclothes.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dang. I think it would have taken me 3 months to come up with that. Thank you. I do have far too many rows and calculations to make for this to run outside of a database but the solution is exactly as requested.
I did make some modifications for portability by using named ranges for obvious reasons. Thank you.
MB
I did make some modifications for portability by using named ranges for obvious reasons. Thank you.
MB
Kevin