Vincnet. wrote:
Hi!
Let's assume your raw data are located in the range A2:C4.

In column E, use the formula:
=IF(ISBLANK(E8),$A$2,IF(SUM($E$1:E8)=SUMPRODUCT($C $2:$C$4,$A$2:$A$4),"",IF(COUNTIF($E$1:E8,E8)<VLOOK UP(E8,$A$2:$C$4,3,FALSE),E8,INDEX($A$2:$A$4,MATCH( E8,$A$2:$A$4,0)+1,0))))

In column F (1st step of calculation):
=IF(E2="","",SUBSTITUTE(VLOOKUP(E2,$A$2:$C$4,2,FAL SE),"""",""))

In column G (2nd step of calculatio):
=IF(F2=F1,SUBSTITUTE(G1,H1&"&",""),F2)

And the result is obtained in column H with:
=IF(ISERROR(FIND("&",G2)),G2,LEFT(G2,FIND("&",G2)-1))

In my example, the whole row 1 is blank...
You obviously can hide columns F and G or send them where you want to...

Does it help?

--
A+

V.


"Dave A" wrote:

Hi all,

after I bit of a tip on manipulating an Excel file

The data is as follows. Column 1 contains numbers; Column two contains
a string of "labels" separated by "&"; Column three contains an integer
that is a count of the number of labels.

I wish to tranpose the labels into a 2nd column with the numbers in
the first column.


Raw data

1234 "fish"&"dog" 2
12356 "cat" & "mouse" 2
1374 "goat"&"horse"&"man" 3


Desired result

1234 fish
1234 dog
12356 cat
12356 mouse
1374 goat
1374 horse
1374 man


Thanks
Dave




Thanks for you efforts.
Didn't seem to work as expected. I'm exploring further

Regards
Dave