Search one column, Retieve multiple rows of data

[A10ECGENT]

Good morning all,

I have a wiring diagram that I am breaking down into a spreadsheet for locating manufacturer part numbers for individual component. My spreadsheet is laid out with wiring harness number on the left. Second column is connector number third column pin number etc. I would like to be able to search a harness number and return al rows associated with that harness. I would like to extend that further by being able to search a connector number and return all rows associated with that number. all of the harnesses have different number of components and connectors can vary from 4 rows to 12 rows. I feel like grouping may help me here but, I have never used grouping and all of the videos that I have found are simply how to group not how to search for a group. I have included a sample of my spreadsheet. Thank You for any help and the opportunity to learn something new

Sample Pic.PNG

Greg
A10ECGent

[AliGW]

Are you still using Excel 2007?

[A10ECGENT]

No, Professional Plus 2016. I forgot to update that in my profile. thanks for catching it.

[AliGW]

Thanks.

Please manually mock up some expected results in the sample workbook.

[A10ECGENT]

I merged some cells in this pic to show the relationships

Sample Pic3.PNG

If I search by harness i Would like to pull all blocks related to that harness number, not just the lines with the harness number.

Sample Pic1.PNG

Same for Connector number. If I search by connector i Would like to pull all blocks related to that connector number, not just the lines with the connector number.

Sample Pic2.PNG

[JeteMc]

Perhaps this will help.
1. Add a column (N) to sheet1 populated using: =IF(Sheet2!A$2<>"",IF(A2<>"",A2,N1),IF(B3<>"",B3,N1))
2. Populate cells B2:G35 on sheet2 using: =IFERROR(INDEX(Sheet1!B$2:B$81,AGGREGATE(15,6,(ROW(Sheet1!C$2:C$81)-ROW(Sheet1!C$1))/(Sheet1!$N$2:$N$81=$A$2),ROWS(B$2:B2))),"")
3. Populate cells J2:J35 and L2:P25 using: =IFERROR(INDEX(Sheet1!A$2:A$81,AGGREGATE(15,6,(ROW(Sheet1!A$2:A$81)-ROW(Sheet1!A$1))/(Sheet1!$N$2:$N$81=$K$3),ROWS(J$2:J2))),"")
Note that if you want to lookup records by harness put the harness number in cell A2 on Sheet2.
If you want to look up records by connector put the connector number in cell K3 on sheet2.
Let us know if you have any questions.

[A10ECGENT]

Thank You. That helped achieve what I was trying to do

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[JeteMc]

You're Welcome and thank you for the feedback. As Ali requests, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.