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!
LVL 2
MMDCiscoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
Please post a workbook with at least 10 rows of data illustrating how you want it to look.

Kevin
BadotzCommented:
Homework?
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.