Extracting Data (ignore duplicates & compile information)

Question

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?

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
– Ralph, Commented Nov 9, 2016 at 22:16

Roger H. · Accepted Answer · 2016-11-14 21:56:52Z

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.

answered Nov 14, 2016 at 21:56

Roger H.

3363 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extracting Data (ignore duplicates & compile information)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related