Jun 24 2022 07:38 AM
Hello
I need to split data from one column in excel to specific columns, depending on what numbers are contained in the combined one.
i.e. for each row, I need to split the comma separated data in column B into the corresponding columns. So, if column B has a "1" in it, it is automatically moved to the column headed 'code 1'. If it has a 2 in it, it moves to the column labelled 'code 2', and so on, for every row. And the numbers (and therefore corresponding columns) will go up to 99.
See example below (but it's an excel worksheet): at the moment, the only columns I have populated are 'ID' and 'Column 1' and I have shown for the data in ID row 1 how I would like it to look.
ID | Column 1 | Code 1 | Code 2 | Code 3 | Code 4 | Code 5 | Code 6 | Code 7 | Code 8 |
1 | 1,3,8,4 | 1 | 3 | 4 | 8 | ||||
2 | 6,2,4,5 | ||||||||
3 | 3,2,4,5,6 |
I have no clue as to how to work in VBA, I am a very basic user of excel.
I hope someone can help :)
Thank you!
Jun 24 2022 08:37 AM
As variant
in D3 is
=IF( COUNTIF($C3,"*" & RIGHT(D$2) & "*"), RIGHT(D$2), "")
and drag it to the right and down.
Jun 24 2022 09:03 AM
Jun 24 2022 09:18 AM
SolutionWhen
=IFERROR( FILTERXML( "<t><s>"&SUBSTITUTE( $C3,",","</s><s>")&"</s></t>","//s[.=" & RIGHT(D$2, LEN(D$2) - FIND(" ",D$2) ) & "]"), "")
Jun 24 2022 10:28 AM
Jun 24 2022 02:15 PM
@sarahhcob , you are welcome
Jun 24 2022 09:18 AM
SolutionWhen
=IFERROR( FILTERXML( "<t><s>"&SUBSTITUTE( $C3,",","</s><s>")&"</s></t>","//s[.=" & RIGHT(D$2, LEN(D$2) - FIND(" ",D$2) ) & "]"), "")