Excel Tip of the Week #196 - Splitting data by category

Hello all and welcome back to the Excel Tip of the Week!  This week we have a Creator post in which we're going to look at solving a particular data manipulation mystery.

The problem

We are dealing with a situation where we have a single table, with one column that divides the data by category, and then one or more other columns that contains the data we care about.  For example, let's say we have sales by salesperson: We want to create one or more separate tables, with only the data for a specific salesperson.  We could create a summary of the data easily enough with a PivotTable (see TOTW #190), but we want to replicate all the original data easily.

The solution

We are going to build on a helper column that will add an identifier for each row: The formula we have used here, assuming that our first label cell is B3, is:

=B3&COUNTIF(\$B\$3:B3,B3)

This will join together two elements: The text of the label itself, and a number indicating which instance of that label this row represents.  Note that one end of the range used for the COUNTIF is dollar-sign fixed, and the other can grow: this will make a steadily expanding field as we move down the original table.

We can now use this identifier to create our reorganised table.  First of all, if we want only a single field (e.g. sale amount): The formula here is:

=IFNA(INDEX(\$C\$3:\$C\$102,MATCH(H\$2&ROW()-2,\$F\$3:\$F\$102,0)),"")

Breaking this down and working from the inside outwards, we have:

1. A concatenation of the column header and a row number (using a ROW function) that will construct the appropriate identifier for that cell (e.g. Angie1 for the top-left cell)
2. A MATCH function that will find the position of the stated identifier (19 in our example as Angie first appears quite far down the source table)
3. An INDEX function that returns the desired data from the given position (in our example, the 19th sale amount value)
4. An IFNA function that will replace any #N/A! errors with a blank - improves the visual appearance (for older versions of Excel you can use IFERROR instead of IFNA)

You can see that I have also included an error check - the formula here is:

=COUNTA(C3:C102)=COUNTIF(H3:T13,">0")

The COUNTA will tell us how many cells have data in the original table.  The blanks in the reorganised table count as non-empty cells, so we have to use a COUNTIF to see how many cells above 0 value we have.  Alternatively we could compare the totals of the two tables in this case.

If we want to extract all the records instead, perhaps for just one salesperson, we can also do that with formulas: The formula used here is:

=IFNA(INDEX(C\$3:C\$102,MATCH(\$H\$17&ROW()-19,\$F\$3:\$F\$102,0)),"")

The formula here is essentially the same structure as before, but the dollar signs are applied a little differently to account for the different layout.

You can explore both solutions in the attached workbook.

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

• Thanks David - works perfectly now!

• Catherine - it's likely an Excel version difference - I used the IFNA function which is not in older versions of Excel.  Replace it with IFERROR and it should work.

• When I open the example spreadsheet, I see lots of #NAME? errors - is this an issue on the Mac?