Find multiple values across multiple worksheets

[jex57]

Test1	Test2	Test3	Test4	Test4
Alignment1	alignment2	alignment1	alignment1	alignment3

<tbody>
</tbody>

Retrieve results as:

Alignment1 - test1 test3 test4
Alignments 2 - test2
Alignment3 - test4



Hi All,
I have workbook with multiple spreadsheets. I would like to be able to search through these workbooks to find multiple results.
In work book1 - Called numbers.

I have column B headings as test names
I have column c headings as alignment names
What I would like to be able to do using a formula in a new worksheet is create my alignment names as rows, then list all the test names that are assigned to that alignment name through my entire workbook

( I was not able to show in multiple tables, but each results would need to be ina new cell and the search would be across multiple workbooks.)
TIA

 

[Special-K99]

If you have the TEXTJOIN facility you should be able to use that otherwise this would have to be VBA.

 

[jex57]

Hi,
Thank you for your response. Do you perhaps know how I can do it in vba?

 

[kang23]

I just posted a similar question. This website has a solution
https://www.get-digital-help.com/2011/07/17/vlookup-across-multiple-sheets-in-excel/

It works but if the users add an additional item in sheet 1, it'll override the first output of sheet 2.
Hope this helps

 

[jex57]

So I have got some code, but now my loops are incorrect. Any ideas on how to fix this?

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">I have workbook with multiple spreadsheets. I would like to be able to search through these workbooks to find multiple results. In work book1 - Called numbers.

• I have column B headings as test names
• I have column c headings as alignment names
• What I would like to be able to do using a formula in a new worksheet is create my alignment names as rows, then list all the test names that are assigned to that alignment name through my entire workbook
  (I was not able to show in multiple tables, but each results would need to be in a new cell and the search would be across multiple workbooks.)

Hi, Sample data in previous post. I have written the code as follows:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Dim countColTracking As Integer
Dim countCol As Integer
Dim countRow As Integer
Dim colName As String
Dim Sheetname As String
Dim Stoploop As Boolean
Dim ws As Worksheet
Dim countSheetR As Integer
Dim WsName As String


For Each ws In ActiveWorkbook.Worksheets
Sheetname = ws.Name
countRow = 7
countColTracking = 2
If Sheetname <> "Sheet1" And Sheetname <> "Infotab" And Sheetname <> "Tracking Sheet" And Sheetname <> "Initialise" And Sheetname <> "ClassList" And Sheetname <> "Template" Then
While Sheets("Tracking Sheet").Cells(countRow, 1) <> ""
colName = Sheets("Tracking Sheet").Cells(countRow, 1)
countCol = 2
Stoploop = False
While Sheets(Sheetname).Cells(2, countCol) <> "Total" And Stoploop = False
WsName = Sheets(Sheetname).Cells(2, countCol)
If Sheets(Sheetname).Cells(4, countCol) = colName Then
Stoploop = True
End If
countCol = countCol + 1
Wend
If Stoploop = True Then 'Value is found - do something

Sheets("Tracking Sheet").Cells(countRow, countColTracking) = Sheets(Sheetname).Cells(2, countCol)
'countColTracking = countColTracking + 1
End If
countRow = countRow + 1
Wend
End If
Next
</code>But now my loops are in the incorrect place. I retrieve Sm Pg8, but I should retrieve SMpg 7 as in the above layouts. It then is still looping incorrectly in that it moves straight to the next column "Answer Questions", instead of moving to the next worksheet
Any ideas?
Thank you
The result should look like this: