QUESTION

Here’s an example an example of Data Parsing in Excel. Phil sent in this challenge. He receives a report and it’s not useful the way it is.

Often, these reports are generated for an executive to read. That same report gets turned over to an Analyst to use to extract information from and make calculations.

So, how do you get from a “Report” to a sortable list?

Here is the original format:

(The information used here is bogus in order to protect sensitive data.
The essence of the problem remains intact.)

Data to Keep

Data to Ditch

6 people are in TX and 2 are in WI The text in the big block
The Carriers Whomever Karl is
Names QQQ
States !!Alpha Foxtrot Indigo
Column headers The codes like J328
Ages The times

 DESIRED RESULT

In order to get from DO NOT WANT to DO WANT there’s a lot of junk in the way that Phil has to manually move or extract. He’s feeling his way through a maze to get where he needs to be. Not only is this tedious and time-consuming, the manual effort increases the chance for errors.

Before we jump into a solution, there are some things to observe to help develop a strategy for Data Parsing in Excel:

The people after the name of a state are all tied to that state Not everyone has a YR JOINED. So, if we sort by that column, the blanks will show up first.
The column headers are above everything else The row that a state is on is not anything we want to keep.
A pattern!
There is a blank between the last person and a new state with a new list of people
The row that starts with a blank is nothing we want to keep

STRATEGY

  • if we can get the states peeled out and moved next to the right names,
  • we could do a sort, and
  • separate the data we need and the data we don’t need.

ANSWER

THE RESULTING FORMULA     =IFERROR(IF(SEARCH(“STATE”,B8),B8,A7),IF(B8=””,””,A7))

When asked what it takes to learn Excel and be good at it, one skill is to be able to explain in English what a formula expresses. Put your hat on ‘cuz here we go!

  • If the cell to the right contains STATE,
  • then copy everything in that cell
  • If that cell does not contain STATE,
  • then copy everything in the cell immediately above.
  • If there’s nothing in the cell above,
  • don’t give me an error message, instead,
  • If the cell to the right is blank,
  • then give me the blank, otherwise
  • give me what’s in the cell immediately above

NOTE: the video is quick. So, please ask any questions in the comments section or the Contact page

Thanks Phil! I hope this helps. Please ask any questions and keep on keepin’ your data clean!

Maze photo credit: FutUndBeidl via photopin cc