concatenating from a different cell within the same row

[method373]

Hey guys, here's another one that's driving me up the wall

We have series of item numbers in a particular column, I need some wa
to find matching item numbers, and, when it finds the match, t
concatenate from a different cell within the same row.

The items in question are necklaces, and we have them organized a
parent and child, the parent has no length listed, But each child is
different available length for that item, a parent item can have an
number if children, so the listing would look something like this:


parent item column Length
006-001 -
006-001 18 inches
006-001 20 inches
006-001 22 inches
006-002 -
006-002 18 inches
006-002 22 inches

An example of what I need would be

parent item column Length
006-001 18 inches, 20 inches, 22 inches
006-001 18 inches
006-001 20 inches
006-001 22 inches
006-002 18 inches, 22 inches
006-002 18 inches
006-002 22 inches

So, I would need it to look in the parent item column, and see whic
ones match, and to take the lengths of all the matches and put the
into another cell.

Thanks for all your help

 

[Max]

A pivot table (PT) approach might be ideal for this ..

Try this:

A sample construct is available at:
http://cjoint.com/?cpe0tQyBWB
PivotTable n Formulas approach_method373_misc.xls

Assume the source table in Sheet1, cols A and B, data from row2 down
( Labels in A1:B1 : parent item column, Length )

Steps: (in Excel 97, my ver, but should be similar)

Select any cell within the source table
Click Data > Pivot Table Report
Click Next > Next

In step 3 of the wiz,
Drag and drop "parent item column" within the ROW area
Drag and drop "Length" within the ROW area, below "parent item column"
Drag and drop "Length" within the DATA area
It should appear as "Count of Length"
Click Finish

The PT will be created in a new sheet to the left, and will yield the
results:

Count of Length Length
parent item column - 18 inches 20 inches 22 inches Grand Total
006-001 1 1 1 1 4
006-002 1 1 1 3
Grand Total 2 2 1 2 7

Click within the PT, then click Format > Autoformat > Classic 2? > OK
to give the PT a nice format

You may find the PT output sufficient for your needs ..

... if not, we could extract it out further to suit your posted end results
using say, empty cols to the right

Paste the labels into H2:I2 : parent item column, Length

Put:

In J3: =IF(C3="","",C$2)
Copy J3 to L3

In H3: =A3

In I3:
=SUBSTITUTE(TRIM(SUBSTITUTE(J3 & K3 & L3,"inches",""))," ",", ")&" inches"

Select H3:L3, copy down to L4

H2:I4 would return the end results posted:

parent item column Length
006-001 18, 20, 22 inches
006-002 18, 22 inches

(think its neater not to repeat the "inches" part in the text under
"Length")

Adapt to suit ..

---

 

[Max]

Oops, correction to line:

Drag and drop "Length" within the ROW area, below "parent item column"

should have read as:

Drag and drop "Length" within the COLUMN area

---

 

[Max]

Here's a link to the sample (corrected description):
http://www.savefile.com/files/3460227
PivotTable_n_Formulas_approach_method373_misc.xls

---

 

[Max]

Re-looked closer at your end result (think I might have mis-interped
earlier)

Tinker with this play
(it might suffice if the max child per parent is 3)

Assume the source table in Sheet1, cols A and B, data from row2 down
( Labels in A1:B1 : parent item column, Length )

Put in C2:
=IF($B2="-",OFFSET($B2,COLUMN(A1),),IF(COLUMN(A1)<2,$B2,""))
Copy C2 to E2, fill down

 

[method373]

Thanks so much, I'm going to play with this tonight, I'll let you know!

You guys are amazing, I swear

 

[Max]

You're welcome !
Thanks for the feedback