• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 791
  • Last Modified:

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$12:$G$2706,0),-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!
0
MMDCisco
Asked:
MMDCisco
  • 2
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
Please post a workbook with at least 10 rows of data illustrating how you want it to look.

Kevin
0
 
BadotzCommented:
Homework?
0
 
MMDCiscoAuthor Commented:
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
0
 
zorvek (Kevin Jones)ConsultantCommented:
Place this array formula in cell C2 and copy to the rest of the result cells:

=IF(SUM((FREQUENCY(IF($B$2:$B$9=$B2,MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1)>0)*1)<=COLUMN()-COLUMN($C$2),"",INDEX($A$2:$A$9,SMALL(IF(FREQUENCY(IF($B$2:$B$9=$B2,MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1),ROW($A$2:$A$9)-ROW($A$2)+1),COLUMN()-COLUMN($C$2)+1)))

Press CTRL+SHIFT+ENTER to enter an array formula.

See the attached workbook.

Kevin
eeclothes.xls
0
 
MMDCiscoAuthor Commented:
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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now