CODEWITHSUNDEEP
excelexcel-formulaexcel-2019
Denis
Denis

Reputation: 1

Need assistance with extracting separate cells from range

I'm facing difficulty with the following and would greatly appreciate advice or a solution. Please see below for details.

  1. I have a range, E43:BV131. E43:BV43 = header, E43:E131 = name column

  2. Cells F44:BV131 contain mainly 4 types of information: either letters "R" or "A" or "G"; or some text in a cell adjacent to an "R" or "A". Example table below.

  3. My task is to extract all cells with "R"/"A" and the text in the adjacent cell.

This is what I have:

enter image description here

This is what I need:

  1. For "R" status

enter image description here

  1. For "A" status

enter image description here

Tried multiple combinations of OFFSET, INDIRECT, INDEX, ROW, COLUMN and a bunch of formulas from found on the web, however, all the documentation I'm finding works around columns whereas I need to extract data from random cells across the table.

Upvotes: 0

Views: 57

Answers (1)

bosco_yip
bosco_yip

Reputation: 3802

Add a line of "Criteria" above "Output header" in row 6

1] In A8, formula copied down :

=IFERROR(INDEX($A$2:$A$4,AGGREGATE(15,6,ROW($A$1:$A$3)/($B$2:$M$4=B$6),ROW(A1))),"")

2] In B8, formula copied down :

=IF(A8="","",B$6)

3] In C8, formula copied down :

=IF(A8="","",INDEX($B$2:$M$4,MATCH(A8,$A$2:$A$4,0),MATCH(B$6,INDEX($B$2:$M$4,MATCH(A8,$A$2:$A$4,0),0),0)+1))

Then, select A8:C9, copied and paste formula to F8:H9

enter image description here

Upvotes: 1

Related Questions