CODEWITHSUNDEEP
excel-formula
Dustin
Dustin

Reputation: 3

How to find a string in array and obtain the corresponding header values?

I'm trying to populate some results for particular string. If you see the example below, I would like to search for string 'Test2' and collect the respective row and column headers. Appreciate quick help!

Student/Date    8/23    8/26    8/27    8/28    8/29    8/30
Dan             Test1   Test3   Test4   Test6   Test3   Test1
Mike            Test3   Test2   Test2   Test2   Test2   Test2
Brian           Test3   Test4   Test1   Test5   Test4   Test3
James           Test4   Test1   Test6   Test3   Test1   Test4
Chris           Test5   Test6   Test5   Test2   Test6   Test5
Paul            Test6   Test5   Test3   Test4   Test5   Test6

Search ResultStudent    Matching Dates              
Test2   Mike            8/26    8/27    8/28    8/29    8/30

Here is the link to the screenshot if the above format doesn't make any sense. https://snag.gy/C06TAB.jpg

enter image description here

Upvotes: 0

Views: 81

Answers (2)

Domenic
Domenic

Reputation: 8124

enter image description here

B10, confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(MMULT(IF($B$2:$G$7=$A13,1,0),TRANSPOSE(COLUMN($B$2:$G$7))^0)>0,1))

B13, confirmed with CONTROL+SHIFT+ENTER, and copied down...

=IF(ROWS(B$13:B13)<=$B$10,INDEX($A$2:$A$7,SMALL(IF(MMULT(IF($B$2:$G$7=$A$13,1,0),TRANSPOSE(COLUMN($B$2:$G$7))^0)>0,ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(B$13:B13))),"")

C13, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IF($B13<>"",IFERROR(INDEX($B$1:$G$1,SMALL(IF(INDEX($B$2:$G$7,MATCH($B13,$A$2:$A$7,0),0)=$A$13,COLUMN($B$1:$G$1)-COLUMN($B$1)+1),COLUMNS($C13:C13))),""),"")

EDIT

For partial matches where the beginning of the string matches the string of interest, as per your example, try the following instead...

B10, confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(MMULT(IF(LEFT($B$2:$G$7,LEN($A13))=$A13,1,0),TRANSPOSE(COLUMN($B$2:$G$7))^0)>0,1))

B13, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(B$13:B13)<=$B$10,INDEX($A$2:$A$7,SMALL(IF(MMULT(IF(LEFT($B$2:$G$7,LEN($A$13))=$A$13,1,0),TRANSPOSE(COLUMN($B$2:$G$7))^0)>0,ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(B$13:B13))),"")

C13, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IF($B13<>"",IFERROR(INDEX($B$1:$G$1,SMALL(IF(LEFT(INDEX($B$2:$G$7,MATCH($B13,$A$2:$A$7,0),0),LEN($A$13))=$A$13,COLUMN($B$1:$G$1)-COLUMN($B$1)+1),COLUMNS($C13:C13))),""),"")

Upvotes: 1

Terry W
Terry W

Reputation: 3277

For formula-based approach @Domenic has already provided something really helpful.

If you are open to an approach using Power Query, which is available in Excel 2010 and later versions, you can follow the steps shown below to obtain the information as required.

Please note I have added two columns of sample data (highlighted in yellow) for testing purpose.

Step by step

The formula you need to manually overwrite the original one after grouping the data is:

= Table.Group(#"Unpivoted Columns", {"Value", "Student/Date"}, {{"Dates", each Text.Combine([Attribute],";"), type text}})

Let me know if you have any questions. Cheers :)

Upvotes: 0

Related Questions