1

I have some data that's structured in a table like this:

existing table.

I have another tab on my spreadsheet where I eliminate any duplicate category entries and want to concatenate the data to look like this:

new table

I need to create this using a function and not a macro or Visual Basic. I've already written the function to eliminate any of the duplicate category entries and now I need to figure out how to take each of those unique categories and append the data behind it. Any ideas?

Community
  • 1
  • 1
krumholz
  • 105
  • 1
  • 3
  • 10
  • Edited to embed the images. This way, if the images are taken off Picasa, the question doesn't become obsolete. – K Mehta Apr 05 '12 at 04:53
  • I believe what would need is to write ***MACRO*** – Fahim Parkar Apr 05 '12 at 04:54
  • possible duplicate of [Excel function to make SQL-like queries on worksheet data?](http://stackoverflow.com/questions/8756802/excel-function-to-make-sql-like-queries-on-worksheet-data) – chris neilsen Apr 05 '12 at 05:42
  • @chrisneilsen I don't think so; while the poster refers to 'a database like query', what they are after is really formatting, not lookup. – KyleNZ Apr 05 '12 at 07:30

1 Answers1

1

Assuming the first image comes from Sheet1 and the second Sheet2, here's a way to do it. You need to add at least one calculated column to Sheet1 and optionally two calculated rows to Sheet2.

  1. Uniquely identify each row on Sheet1. Add a new column A with the formula =B3&COUNTIF($B$3:B3,B3) in A3 and fill down. You'll have first1 in A3, first2 in A4 and so on.
  2. Identify each block of columns on Sheet2. Add a new row 6 with the formula =COUNTIF($B$2:B2,B2) in B6 and fill across. The values will be 1,1,1,2,2,2,3,3,3. You can type these in manually if you like but it won't scale as easily.
  3. Work out the column of each data item in Sheet1 and put in the columns for Sheet2. Add a new row 7 to Sheet2 with the formula =MATCH(B2,Sheet1!$A$2:$E$2,0) and fill across. The values will be 3,4,5,3,4,5,3,4,5. We now know, for each cell in Sheet2, which instance of the category we have and in which column the data variable comes from.
  4. Use VLOOKUP to find first1 and return data column 3 within the data table. On Sheet2, set cell B3 to =VLOOKUP($A3&B$6,Sheet1!$A$2:$E$8,B$7,FALSE) and fill down and across.
  5. You'll have a number of #N/A errors (e.g. cell E4). If you have a newer version of Excel, you can use IFERROR() to escape this; otherwise, use =IF(ISNULL(<formula>), "", <formula>) where <formula> is the formula in step 4.

To summarise:

  • Create a unique, sequential, calculable row ID for each row in the source.
  • Determine which instance of that sequence is used by each column in the destination. This is appended to the end of the category to determine the row IDs formed in the previous step.
  • Determine which column contains the data for each column in the destination.
  • Look up the row with the calculated row ID and find the relevant column for this data item.
  • Format the results appropriately.
KyleNZ
  • 396
  • 1
  • 9