Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
Open Excel 2003 from Windows Explorer | Excel Discussion (Misc queries) | |||
Need suggestions for some uses of Ms Excel | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |