separate jumbled up data

[maykoko]

hi everyone.

i have a system generate field that contain the product code that was sold by the sale person, however the product code and sale person are in the same column.
Is it possible to separate them in excel so that there is a different column for "AMY" & ALEX
gofile.io/d/pmA0il
Thank you.

Date Sale Person
01-Jan AMY
01-Jan 5
04-Jan 94
04-Jan ALEX
04-Jan 89
04-Jan 9
07-Jan 23
08-Jan ALEX
08-Jan 88
08-Jan 17
11-Jan 57
12-Jan ALEX

Final result look something like this?

 

[jtakw]

Hi,

Here is one way, formula copied down and across:

NOTE: You can hide Column B.

Book3.xlsx
	A	B	C	D
1	Date	Sale Person	AMY	ALEX
2	1/1/2021	AMY
3	1/1/2021	5	5
4	1/4/2021	94	94
5	1/4/2021	ALEX
6	1/4/2021	89		89
7	1/4/2021	9		9
8	1/7/2021	23		23
9	1/8/2021	ALEX
10	1/8/2021	88		88
11	1/8/2021	17		17
12	1/11/2021	57		57
13	1/12/2021	AMY
14	1/12/2021	89	89
Sheet874

Cell Formulas
Range	Formula
C2:D14	C2	=IF($B2=C$1,"",IF(LOOKUP("zzz",$B$2:$B2)=C$1,$B2,""))

 

[maykoko]

Hi, jtakw, thanks so much it work.

By the way, can this work for data other than numbers..such as below.
As it was a system system generated file, we had no idea what type of data will appeared, but we considered it as 1 transaction.
Is there a way excel can output these regardless of type of data (Alex AMY are fixed field)

19-Jan ALEX
19-Jan D567
20-Jan X88
21-Jan 124
22-Jan ALEX
22-Jan P99
22-Jan AMY
22-Jan V67

Attached is the file Gofile - Free file sharing and storage platform
Thank you very much.

 

[jtakw]

Ok, throwing in a wrench?

This should work:

Book3.xlsx
	A	B	C	D
1	Date	Sale Person	AMY	ALEX
2	1-Jan	AMY
3	1-Jan	5	5
4	4-Jan	94	94
5	4-Jan	ALEX
6	4-Jan	89		89
7	4-Jan	9		9
8	7-Jan	23		23
9	8-Jan	ALEX
10	8-Jan	88		88
11	8-Jan	17		17
12	11-Jan	57		57
13	12-Jan	ALEX
14	12-Jan	AMY
15	12-Jan	89	89
16	14-Jan	59	59
17	15-Jan	62	62
18	16-Jan	54	54
19	17-Jan	27	27
20	19-Jan	ALEX
21	19-Jan	D567		D567
22	20-Jan	X88		X88
23	21-Jan	124		124
24	22-Jan	ALEX
25	22-Jan	P99		P99
26	22-Jan	AMY
27	22-Jan	V67	V67
Sheet876

Cell Formulas
Range	Formula
C2:D27	C2	=IF($B2=C$1,"",IF(LOOKUP(2,1/(($B$2:$B2=$C$1)+($B$2:$B2=$D$1)),$B$2:$B2)=C$1,$B2,""))

 

[maykoko]

wow, genius. ?
Really helped me a lot, all the while, i have been doing it manually.

Thank you so much!

 

[maykoko]

So sorry,can I ask throw in 1 last wrench??
I tried the formula to see if I can get the following, but keep having error.
In this case, I only need AMy column but will need the word 'AMY'.
Is it possible?
could you help me?

 

[maykoko]

Ahhh...i think i managed to do workaround with a new dataset given.
Thank anyway

 

[jtakw]

So sorry,can I ask throw in 1 last wrench??
I tried the formula to see if I can get the following, but keep having error.
In this case, I only need AMy column but will need the word 'AMY'.
Is it possible?
could you help me?

It's just a simple modification to my formula above, change the first part,

=IF($B2=C$1,"",..........................

to

=IF($B2=C$1,$B2,.....................

Book3.xlsx
	A	B	C	D
1	Date	Sale Person	AMY	ALEX
2	1-Jan	AMY	AMY
3	1-Jan	5	5
4	4-Jan	94	94
5	4-Jan	ALEX		ALEX
6	4-Jan	89		89
7	4-Jan	9		9
8	7-Jan	23		23
9	8-Jan	ALEX		ALEX
10	8-Jan	88		88
11	8-Jan	17		17
12	11-Jan	57		57
13	12-Jan	ALEX		ALEX
14	12-Jan	AMY	AMY
15	12-Jan	89	89
16	14-Jan	59	59
17	15-Jan	62	62
18	16-Jan	54	54
19	17-Jan	27	27
20	19-Jan	ALEX		ALEX
21	19-Jan	D567		D567
22	20-Jan	X88		X88
23	21-Jan	124		124
24	22-Jan	ALEX		ALEX
25	22-Jan	P99		P99
26	22-Jan	AMY	AMY
27	22-Jan	V67	V67
Sheet876

Cell Formulas
Range	Formula
C2:D27	C2	=IF($B2=C$1,$B2,IF(LOOKUP(2,1/(($B$2:$B2=$C$1)+($B$2:$B2=$D$1)),$B$2:$B2)=C$1,$B2,""))

If you don't want ALEX in the ALEX column, use the original version of the formula for ALEX column.