Combining a column's data by referencing another

[jimmy coleman]

I've received warehouse inventory and need to reorganize the data into the appropriate columns. My specific issue is concerning the column named new_description

The file has :

column A with part numbers, some are the same, some are not. (ie. 1,1,1 2,2,3,4...7)
column C with a description different in every row. (ie. red, blue, green.....)

column F must have the following info:

for all part numbers that repeat I want to add (concatenate?) their description into a single cell beginning and separated by "<li>" (ie. for part number 1 there are three entries with 3 descriptions each containing a different color; in column F I'd like it to say <li>red<li>blue<li>green)

I can do it manually as I show in the attached file.... however the full list is 5000 products or more in a few separate files so a formula to help automate this is crucial.

Can anyone offer some direction or a formula to help me eliminate as much repetition as possible?

[Richard Buttrey]

Hi,

The attached is one solution.

I took a copy of your original sheet, demerged all the cells and then sorted columns A:C using A as the primary sort.

Then I added the three new columns D:F with the formulae that you see.

Now all you need to do (I haven't completed this step), is to copy columns E&F with a paste special values, then sort these two columns on the new part number.

Obviously if it's something you'll have to repeat on many occasions it would be a good idea to write a macro to do it. However if it's a one off just adopt the technique above.

HTH

[DonkeyOte]

Another option, again using formulae, but using original setup:

H3: 
=IF(ISNUMBER(A3),IF(A3=A1,H2,"")&"<li>"&C3,H2)
copied down

F3:
=IF(OR($A3="",COUNTIF($E$3:$E3,$E3)>1),"",LOOKUP(2,1/($A$3:$A$1000=$A3),$H$3:$H$1000))
copied down

change ranges to suit..

[jimmy coleman]

Another option, again using formulae, but using original setup:

H3: 
=IF(ISNUMBER(A3),IF(A3=A1,H2,"")&"<li>"&C3,H2)
copied down

F3:
=IF(OR($A3="",COUNTIF($E$3:$E3,$E3)>1),"",LOOKUP(2,1/($A$3:$A$1000=$A3),$H$3:$H$1000))
copied down

change ranges to suit..

DonkeyOte, that is precisely what I was looking for. It worked perfectly though I've been looking at the formula for 20 minutes and still don't quite understand it, so I never would have come up with that trying to make the fanciest IF/THEN formula I could think of, since its the only formula that is intuitive enough for me to try. I prefer your solution over Richard Buttrey because it requires no changing of the cell's formatting (the annoyingly merged cells that the list originally has) and also I can avoid using a macro which I have never used before. I'll have to research a lil more before I start asking how in the world to even use macros.

Thanks very much!

[devtech]

Guys,
I need a help on merging two excel sheet,please find the description below.
I have two excel sheet named mol.xls and code.xls.
At mol.xls the column header are activity,element,comment,owner & version.
And at code.xls the column header are-owner,total,added,deleted & changed.
I need to merge the sheet code.xls to mol.xls with refernce to the key as "Owner".Like for the owner n.vakkalagadda it will serch the code.xls and copy the data from it and paste into right at mol.xls with the header "total",added,deleted & changed.
Please kindly reply if any suitable solution,macro or Vlook up.I have attached the sheets and the result sheet(what I require) for your reference.

[DonkeyOte]

Your post does not comply with Rule 2 of our Forum RULES.

Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

[devtech]

I appologize for my mistake.I have posted my query at a new thread.
Thank you very much Sir..