0

I have a lot of data that I am trying to summarize into a new sheet and the solution is evading me. I have a data set that looks similar (although larger and more complex) to the attached image.

I have figured out how to make the first list to remove duplicates, as my problem started out similar to the problem here. (i.e. using the =INDEX($A$2:$A$13, MATCH(0, COUNTIF($F$2:F2, $A$2:$A$13&""), 0) formula).

What I cant figure out how to do now though is bring in the respective data for each mini table within my original data set. My first thought was to use Hlookup/Vlookup, but they require the headers to be the data you are looking for, not interspaced throughout your table..

PLEASE NOTE - My '111, 222, 333, ...' series is directly related to each other (i.e. dates, plus 1 each time) and so creating those at the top of each column is a simple task, and already completed.

Is this possible?

Community
  • 1
  • 1
Jared Murphy
  • 1
  • 1
    are they always grouped in threes? – Scott Craner Nov 09 '16 at 19:44
  • This would be so much easier with VBA. Still, would you mind elaborating a bit more on the structure of the "mini tables" (as already pointed out by Scott)? Are they always three columns wide and three rows down? Or can they differ in size and the `111` and `222` and `333` should be automatically detected? If so, how do these values differ from the data in the mini-tables (in order to distinguish between column headers and data within the mini-tables? – Ralph Nov 09 '16 at 22:16

1 Answers1

1

A three-celled array formula will do it. For "AAA" select F2:H2 then type in ==IFERROR(INDEX($B$2:$D$12,MATCH("AAA",$A$2:$A$4,0),0),"----"),then =IFERROR(INDEX($B$2:$D$12,MATCH("AAA",$A$6:$A$8,0),0),"----") in I2:K2,then =IFERROR(INDEX($B$2:$D$12,MATCH("AAA",$A$10:$A$12,0),0),"----") in L2:N2. You will have to manually change "AAA"TO "BBB" as you copy and paste each three-celled array to the next row down to account for each three-lettered variation. It would be quicker to us Excel's REPLACE tool : Replace "AAA" with "BBB" and so on for each new row. As these are array formulas, you will not be able to simply drag down- use copy and paste instead. I thoroughly tested these formulas and they work as I expected.The cell references that I used were purely random, of course.

Roger H.
  • 326
  • 3
  • 7