Splitting a column of data into corresponding columns

[SSCOB]

Hello
I have a column of data containing numbers separate by commas which I need to split out into corresponding columns, as per:

Codes​

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

13​

14​

15​

16​

17​

18​

19​

20​

1,5,12,16,20​

1​

5​

12​

16​

20​

1,5,12,16,20​

1​

1​

1​

1​

1​

So, for example, in Cell A2 I have the numbers 1, 5, 12,16, 20,
Cell B2 is labelled 1, Cell C2 is labelled 2, Cell D2 is labelled 3, and so on up to say 20.
I then need to be able to move the numbers in A2 into the corresponding cells, so A2 above would then be split so that the '1' appears as '1' in the column labelled 1. the number 5 appears as 5 under the column labelled 5, and so on. Any other cells would remain blank.
I would also like to be able to do similar, but instead of the A3 numbers transferring as that number, I just need a 1.
E.g. Cell A3 above would then show as a 1 in the column labelled 1, a 1 in the column labelled 5, a 1 in the column labelled 12, etc.

Thank you!!!

 

[duggie33]

Are you looking to do this using formulas or VBA?

Doug

 

[duggie33]

You could try this with a formula...

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Codes	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
2	1,5,12,16,20	1				5							12				16				20
3	1,5,12,16,20	1				1							1				1				1
Sheet18

Cell Formulas
Range	Formula
B2:U2	B2	=IF(ISNUMBER(MATCH(B1:U1,VALUE(TEXTSPLIT(A2,",")),0)),B1:U1,"")
B3:U3	B3	=IF(ISNUMBER(MATCH(B1:U1,VALUE(TEXTSPLIT(A3,",")),0)),1,"")
Dynamic array formulas.

Hope that helps,

Doug

 

[DanteAmor]

Hi @SSCOB and welcome to MrExcel:
In cell B3:

=IFERROR(IF(MATCHX(B$2:U$2,TEXTSPLIT(A3&",",,",")+0,0),B$2:U$2),"")

In cell B4:

=FERROR(IF(MATCHX(B$2:U$2,TEXTSPLIT(A3&",",,",")+0,0),1),"")

 

[SSCOB]

Hi @SSCOB and welcome to MrExcel:
In cell B3:

=IFERROR(IF(MATCHX(B$2:U$2,TEXTSPLIT(A3&",",,",")+0,0),B$2:U$2),"")

In cell B4:

=FERROR(IF(MATCHX(B$2:U$2,TEXTSPLIT(A3&",",,",")+0,0),1),"")

Thank you!!!

 

[SSCOB]

You could try this with a formula...

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Codes	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
2	1,5,12,16,20	1				5							12				16				20
3	1,5,12,16,20	1				1							1				1				1
Sheet18

Cell Formulas
Range	Formula
B2:U2	B2	=IF(ISNUMBER(MATCH(B1:U1,VALUE(TEXTSPLIT(A2,",")),0)),B1:U1,"")
B3:U3	B3	=IF(ISNUMBER(MATCH(B1:U1,VALUE(TEXTSPLIT(A3,",")),0)),1,"")
Dynamic array formulas.

Hope that helps,

Doug

Perfect, thank you!!

 

[duggie33]

No problem, glad to help.

Doug