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 way
to find matching item numbers, and, when it finds the match, to
concatenate from a different cell within the same row.

The items in question are necklaces, and we have them organized as
parent and child, the parent has no length listed, But each child is a
different available length for that item, a parent item can have any
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 which
ones match, and to take the lengths of all the matches and put them
into another cell.

Thanks for all your help!


--
method373
------------------------------------------------------------------------
method373's Profile: http://www.excelforum.com/member.php...o&userid=29888
View this thread: http://www.excelforum.com/showthread...hreadid=512490

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"method373" wrote
in message ...

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 way
to find matching item numbers, and, when it finds the match, to
concatenate from a different cell within the same row.

The items in question are necklaces, and we have them organized as
parent and child, the parent has no length listed, But each child is a
different available length for that item, a parent item can have any
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 which
ones match, and to take the lengths of all the matches and put them
into another cell.

Thanks for all your help!


--
method373
------------------------------------------------------------------------
method373's Profile:
http://www.excelforum.com/member.php...o&userid=29888
View this thread: http://www.excelforum.com/showthread...hreadid=512490

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Here's a link to the sample (corrected description):
http://www.savefile.com/files/3460227
PivotTable_n_Formulas_approach_method373_misc.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"method373" wrote
in message ...

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 way
to find matching item numbers, and, when it finds the match, to
concatenate from a different cell within the same row.

The items in question are necklaces, and we have them organized as
parent and child, the parent has no length listed, But each child is a
different available length for that item, a parent item can have any
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 which
ones match, and to take the lengths of all the matches and put them
into another cell.

Thanks for all your help!


--
method373
------------------------------------------------------------------------
method373's Profile:
http://www.excelforum.com/member.php...o&userid=29888
View this thread: http://www.excelforum.com/showthread...hreadid=512490

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

You guys are amazing, I swear;) ;) ;) ;)


--
method373
------------------------------------------------------------------------
method373's Profile: http://www.excelforum.com/member.php...o&userid=29888
View this thread: http://www.excelforum.com/showthread...hreadid=512490

You're welcome !
Thanks for the feedback
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"method373" wrote
in message ...

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

You guys are amazing, I swear;) ;) ;) ;)