Assume a situation: I requested for spare parts available for many machines. My team had compiled that data from various
sources and provided me as shown in below format.
Column B contains the part for
each machine but what they did is machine number is entered in one cell only
and its parts are spread in multiple cells in column B. Note that it’s data of thousands
of machinery, I cannot really do any analysis on this data. For example: I cannot
apply AutoFilter to see how many parts are available for a machine neither can
I apply a pivot to summarize the data. In order to do that, I need to have the
machine numbers against each part. How do you get this done?
Fortunately, we can do this very
quickly in Excel. It only takes couple of seconds. Let’s see how.
Select all the cells of column A that
has corresponding value in column B. Press Ctrl+G or F5. This opens up a
dialogue box Go To. Click on Special in it, in the new box, check
the radio button that reads Blanks and
click OK. You should be able see all
blank cells are selected as in below picture.
Now, start typing = and click on
first non-blank cell above where = is placed and press Enter. In my example, when I type =, it automatically be placed in
A3 and I click on cell A2 which is immediate non-blank cell above A3.
If I press Enter now, value in A2 will be placed in A3.
Now just press Ctrl+D or Home tab
> Editing group > Fill > Down (Excel 2007 or later) or Edit menu >
Fill > Down (Excel 2003 or earlier). This action should populate all the
blank cells with immediate non-blank cells above.
One more (need based) step
involves copying entire A column and paste as values in order to remove
formulas. Now I can play with this data. Enjoy!
No comments:
Post a Comment