Excel tip
 

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

Excel tip
 

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

Excel tip
 

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

Excel tip
 

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

Just a couple of questions to clarify, are your "labels" in column 1
unique or might they repeat, e.g. you show 1234 on the first line,
could 1234 occur again?

Do you actually have quotes around each label as shown? You don;t want
these in the final data?

You show line 2 with spaces either side of the & - is this a typo,
other lines show no spaces?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=554789

Excel tip
 

Just a couple of questions to clarify, are your "labels" in column 1
unique or might they repeat, e.g. you show 1234 on the first line,
could 1234 occur again?

Do you actually have quotes around each label as shown? You don;t want
these in the final data?

You show line 2 with spaces either side of the & - is this a typo,
other lines show no spaces?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=554789

daddylonglegs

to answer your questions
The numbers in column a will be unique.
I do have quotes around each label in the raw data, however I have been
using "Text to Columns" to
separate each label into columns (removing the "&'s" and the quotes) .
I have also move the last column in the example above to column "A" as
after step describe above.
Raw Data now looks as follows


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


There are no spaces.

Thanks
Dave