advanced searching or sorting through jumbled data

AleinPump

New Member
Joined
Aug 30, 2018
Messages
2
Hi team,

I am jammed with a situation and I was hoping that somebody can help me out.

I gave a team a sheet to fill out, where they had to copy a row with 3 categories, located in 3 columns, and asked them to copy+paste those 3 categories next to the other input I was requesting.
Unfortunately, most pasted all kinds of versions of those 3 columns jumbled into 1 cell only, for the most part by combining the categories in different ways. Some used tabs as text (not sure if Excel sees those as tabs or a bunch of spaces), others used the ">" character, some used "/"'s and " "'s...

Essentially I am trying to find a way to count how many times the combination A2, B2, and C3 was used in column E:

ABCDE
1​
Cat 1Cat 2Cat 3examples of jumbled input
2​
blah 1qwerty 1asdf 1blah 1qwerty 1asdf 1
3​
blah 2qwerty 2asdf 2blah 2qwerty 2asdf 2
4​
blah 3qwerty 3asdf 3blah 3qwerty 3asdf 3
5​
blah 4qwerty 4asdf 4blah 4qwerty 4asdf 4
6​
blah 5qwerty 5asdf 5blah 5qwerty 5asdf 5
7​
blah 6qwerty 6asdf 6blah 6>qwerty 6>asdf 6
8​
blah 7qwerty 7asdf 7blah 7>qwerty 7>asdf 7
9​
blah 8qwerty 8asdf 8blah 8>qwerty 8>asdf 8
10​
blah 9qwerty 9asdf 9blah 9>qwerty 9>asdf 9
11​
blah 10qwerty 10asdf 10blah 10>qwerty 10>asdf 10
12​
blah 11qwerty 11asdf 11blah 11/qwerty 11/asdf 11
13​
blah 12qwerty 12asdf 12blah 12/qwerty 12/asdf 12
14​
blah 13qwerty 13asdf 13blah 13/qwerty 13/asdf 13
15​
blah 14qwerty 14asdf 14blah 14/qwerty 14/asdf 14
16​
blah 15qwerty 15asdf 15blah 15/qwerty 15/asdf 15
17​
blah 16qwerty 16asdf 16blah 16 qwerty 16 asdf 16
18​
blah 17qwerty 17asdf 17blah 17 qwerty 17 asdf 17
19​
blah 18qwerty 18asdf 18blah 18 qwerty 18 asdf 18
20​
blah 19qwerty 19asdf 19blah 19 qwerty 19 asdf 19
21​
blah 20qwerty 20asdf 20blah 20 qwerty 20 asdf 20


Any advice as to how I can get a count on those categories, without having to manually fix everything in E line by line (got a few thousand lines of the input and around 300 categories in the ABC)?

Thanks very much in advance for the help!!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,238
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
in the Data menu use the Text To Columns feature …​
 

Forum statistics

Threads
1,141,062
Messages
5,704,055
Members
421,325
Latest member
tapete86

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