Excel tip

[Dave A]

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

 

[Guest]

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)<VLOOKUP(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,FALSE),"""",""))

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?

 

[Dave A]

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)<VLOOKUP(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,FALSE),"""",""))

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?

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

Regards
Dave

 

[daddylonglegs]

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?

 

[Dave A]

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

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