Extracting Data (ignore duplicates & compile infor

问题:

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?

回答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.