separate jumbled up data

maykoko

New Member
Joined
Mar 27, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Attachments

  • 123.JPG
    123.JPG
    20.1 KB · Views: 13

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,097
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Here is one way, formula copied down and across:

NOTE: You can hide Column B.

Book3.xlsx
ABCD
1DateSale PersonAMYALEX
21/1/2021AMY  
31/1/202155 
41/4/20219494 
51/4/2021ALEX  
61/4/202189 89
71/4/20219 9
81/7/202123 23
91/8/2021ALEX  
101/8/202188 88
111/8/202117 17
121/11/202157 57
131/12/2021AMY  
141/12/20218989 
Sheet874
Cell Formulas
RangeFormula
C2:D14C2=IF($B2=C$1,"",IF(LOOKUP("zzz",$B$2:$B2)=C$1,$B2,""))
 
Last edited:

maykoko

New Member
Joined
Mar 27, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
6,097
Office Version
  1. 2016
Platform
  1. Windows
Ok, throwing in a wrench? :sneaky:

This should work:

Book3.xlsx
ABCD
1DateSale PersonAMYALEX
21-JanAMY  
31-Jan55 
44-Jan9494 
54-JanALEX  
64-Jan89 89
74-Jan9 9
87-Jan23 23
98-JanALEX  
108-Jan88 88
118-Jan17 17
1211-Jan57 57
1312-JanALEX  
1412-JanAMY  
1512-Jan8989 
1614-Jan5959 
1715-Jan6262 
1816-Jan5454 
1917-Jan2727 
2019-JanALEX  
2119-JanD567 D567
2220-JanX88 X88
2321-Jan124 124
2422-JanALEX  
2522-JanP99 P99
2622-JanAMY  
2722-JanV67V67 
Sheet876
Cell Formulas
RangeFormula
C2:D27C2=IF($B2=C$1,"",IF(LOOKUP(2,1/(($B$2:$B2=$C$1)+($B$2:$B2=$D$1)),$B$2:$B2)=C$1,$B2,""))
 
Solution

maykoko

New Member
Joined
Mar 27, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Thank you so much!
 

maykoko

New Member
Joined
Mar 27, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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?

AMY.JPG
 

maykoko

New Member
Joined
Mar 27, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Ahhh...i think i managed to do workaround with a new dataset given.
Thank anyway
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,097
Office Version
  1. 2016
Platform
  1. Windows
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
ABCD
1DateSale PersonAMYALEX
21-JanAMYAMY 
31-Jan55 
44-Jan9494 
54-JanALEX ALEX
64-Jan89 89
74-Jan9 9
87-Jan23 23
98-JanALEX ALEX
108-Jan88 88
118-Jan17 17
1211-Jan57 57
1312-JanALEX ALEX
1412-JanAMYAMY 
1512-Jan8989 
1614-Jan5959 
1715-Jan6262 
1816-Jan5454 
1917-Jan2727 
2019-JanALEX ALEX
2119-JanD567 D567
2220-JanX88 X88
2321-Jan124 124
2422-JanALEX ALEX
2522-JanP99 P99
2622-JanAMYAMY 
2722-JanV67V67 
Sheet876
Cell Formulas
RangeFormula
C2:D27C2=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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,120
Messages
5,768,224
Members
425,460
Latest member
Astros1243

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top