how to change the order of the values

[cegl529]

Please, help me with the following problem:

[pre]

A    B
1  a    1,2,3
2  b    1,4

solve as:
A    B
1  1    a,b
2  2    a
3  3    a
4  4    b

[/pre]
Tanks in advance for the solution.

 

[Sajan]

@cegl529,

Without knowing much about what you are attempting to do, I would like to suggest the following simple solution. If this does not work due to some constraints in your problem domain, please describe your actual problem in more detail, and we can supply more sophisticated formulas.


I have assumed the following data in A1:B4:

[pre]

a	1,2,3, 4,2
b	1,4
c	2,3
d	4,5,3
For ease of reference, I have named the ranges as follows:

alphadata = A1:A4

numdata = B1:B4


Copy / Paste transpose the range A1:A4 to cells L1:O1


Put the value 1 into cell K2

In cell K3, put the formula =K2+1

Copy the cell K3 to additional rows


In cell L2, put the following array formula:

=IFERROR(IF(MATCH(L$1,IF(ISNUMBER(FIND($K2,numdata)),alphadata),0), L$1), "")


enter with Ctrl + Shift + Enter


Copy cell L2 to the other cells (so that you have the formula in range L2:O6)


You should get results similar to the following:

ZZ      a	b	c	d
1	a	b
2	a		c
3	a		c	d
4	a	b		d
5				d

[/pre]
Cheers,

Sajan.


Edit: I added ZZ to ensure the columns align in the sample output. You can ignore the ZZ