Help Populating a Table

[TripletDad]

OK - I need help creating / populating a table. I can talk it through, but can't figure out how to work the formula...

In the table below, the left half is the data I currently have. I need to wiggle it around to look like J10.

Basically, I need a formula that I can plug into every cell in Column J that says at the value to the immediate left in Column I. Take this value and look at Column E... then every time there is match, take the value in Column D, and find the next match. Put pipes between each value.

Basically, I am trying to get a row for each parent ID that has all the children in one cell delineated by pipes.
I am trying to get a row for each parent ID that has all the children in one cell delineated by pipes.

Any thoughts?

*	D	E	F	G	H	I	J
7	Spreadsheet I have now	*	*	*	*	Spreadsheet I need to help to complete *(J10 is a sample)	*
8	*	*	*	*	*	*	*
9	SKU Number	Parent ID	*	*	*	Parent ID	*
10	88881	10	*	*	*	10	88881|88882|88883|88884|88885
11	88882	10	*	*	*	20	*
12	88883	10	*	*	*	30	*
13	88884	10	*	*	*	40	*
14	88885	10	*	*	*	60	*
15	88886	20	*	*	*	*	*
16	88887	20	*	*	*	*	*
17	88888	20	*	*	*	*	*
18	88889	20	*	*	*	*	*
19	88890	20	*	*	*	*	*
20	88891	30	*	*	*	*	*
21	88892	30	*	*	*	*	*
22	88893	30	*	*	*	*	*
23	88894	30	*	*	*	*	*
24	88895	30	*	*	*	*	*
25	88896	40	*	*	*	*	*
26	88897	40	*	*	*	*	*
27	88898	60	*	*	*	*	*
28	88899	60	*	*	*	*	*
29	88900	60	*	*	*	*	*

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[cyrilbrd]

Assuming SKU in Column D, Parent ID in Column E, Parent ID recap in I10 going down.
In J10 type:
=IF(COLUMNS($J10:J10)>COUNTIF($E:$E,$I10),"",INDEX($D:$D,SMALL(IF($E$10:$E$29=$I10,ROW(E$10:E$29)),COLUMNS($J10:J10))))
Ctrl + Shift + Enter, not just enter.
Copy Down till needed and copy right till needed.
This would give:

    Parent ID              
10  88881  88882  88883  88884  88885    
20  88886  88887  88888  88889  88890    
30  88891  88892  88893  88894  88895    
40  88896  88897          
60  88898  88899  88900

Would that work for you?

 

[TripletDad]

This worked perfectly! Thank you!!!

 

[cyrilbrd]

You are welcome. Thanks for the feedback.