VLOOKUP across multiple sheets with multiple values returned

[Darth_Sullivan]

Let me start by saying I am using Excel 2002.

I want to create a summary sheet in my workbook that contains 31 other sheets. Each sheet corresponds to the day of the given month, "Day 1" "Day 2" etc. On my summary sheet I want to search every sheet for "NL" which would be located in cells A14:B20 in any given sheet. If it matters, the cells in column A and B are merged in that portion I am wanting excel to search.

The value I want returned will be in column E with the matching row. I want Excel to return each instance that "NL" is found with the corresponding data from column E.

To make matters even more complicated, the search criteria "NL" is not going to be the only thing listed in the cell. It may be "2/5 NL" or "2/4 NL" or maybe even "2/5 No Limit." The key words I want excel to find are either "NL" or "No Limit" as they mean the same for this workbook. I do want excel to return all instances of it finding either "NL" or "No Limit" which would be several and would increase as the month progresses.

Is this possible or am I asking excel to do too much? I've tried searching the forums for a solution that I could apply to my specific problem, but end up running into problems and I think it is because of the merged cells. I am not sure though hence my posting here.

Thank you for any help in this matter.

 

[L. Howard]

Swap this segment of code out for the code you are using.

For i = LBound(MyArr) To UBound(MyArr)
   'With MyArr(i)
   With Sheets(MyArr(i))
      '//  lr = .Cells(.Rows.Count, 5).End(xlUp).Row
      Set rngB = .Range("E13:E20")

      For Each c In rngB
         If c > 0 Then
            varOut = c.Offset(, -4)
            Sheets("Sheet1").Range("A" & Rows.Count) _
            .End(xlUp)(2) = varOut
            varOut1 = c
            Sheets("Sheet1").Range("B" & Rows.Count) _
            .End(xlUp)(2) = varOut1
         End If
Next 'c

  End With
Next 'i

Application.ScreenUpdating = False

The code would essentially replace everything between these two lines.

Application.ScreenUpdating = True

Howard

 

[Darth_Sullivan]

Swap this segment of code out for the code you are using.

For i = LBound(MyArr) To UBound(MyArr)
   'With MyArr(i)
   With Sheets(MyArr(i))
      '//  lr = .Cells(.Rows.Count, 5).End(xlUp).Row
      Set rngB = .Range("E13:E20")

      For Each c In rngB
         If c > 0 Then
            varOut = c.Offset(, -4)
            Sheets("Sheet1").Range("A" & Rows.Count) _
            .End(xlUp)(2) = varOut
            varOut1 = c
            Sheets("Sheet1").Range("B" & Rows.Count) _
            .End(xlUp)(2) = varOut1
         End If
Next 'c

  End With
Next 'i

Application.ScreenUpdating = False

The code would essentially replace everything between these two lines.

Application.ScreenUpdating = True

Howard

Thanks Howard, this does everything I needed it to do with my data. Is it possible to have excel running this macro automatically so that it updates the listed data itself? Is it then also possible to have excel clear that data listed to Sheet1 before running the macro automatically so that I don't end up with excel just duplicating my data every time new data is enter to my other sheets?

 

[Darth_Sullivan]

Are you looking for the first match? What is involved - numbers or text?

Sorry I did not respond Aladin, I was wanting every match. Text was in column A and Numbers in column E. Howard's solution has solved that first problem i was having. Thank you for your time too.

 

[L. Howard]

Is it possible to have excel running this macro automatically so that it updates the listed data itself? Is it then also possible to have excel clear that data listed to Sheet1 before running the macro automatically so that I don't end up with excel just duplicating my data every time new data is enter to my other sheets?

I think we can run the code automatically using a Worksheet_Change event.

For that to work we need something on the sheet 1 "to change". Do you know what might change on sheet 1 to make it fire a change event macro.

Maybe another way to ask is, what do you do on sheet 1 that makes you know now is the time for me to click the button to run the x-fer code?

Regarding clearing the data. Do you mean clearing sheet 1 of the last imported data (col A and B) of the 30 other sheets before the code runs and brings in another set of data to sheet 1?

If you have one of the Drop Box type facilities, where you could post a link to an example workbook, that would be nice. Would not need (or want) all 30 "other" sheets, but say two or three would be plenty to get a fuller grasp on the whole concept.

Along with some detailing of what happens and where.

Howard

 

[Darth_Sullivan]

I think we can run the code automatically using a Worksheet_Change event.

For that to work we need something on the sheet 1 "to change". Do you know what might change on sheet 1 to make it fire a change event macro.

Maybe another way to ask is, what do you do on sheet 1 that makes you know now is the time for me to click the button to run the x-fer code?

Sheet 1 is simply acting as a summary page. All data is input by employees into the other sheets. I would like to be able to just look at sheet 1 and view a quick summary of information.

Regarding clearing the data. Do you mean clearing sheet 1 of the last imported data (col A and B) of the 30 other sheets before the code runs and brings in another set of data to sheet 1?

What I mean here is: when the macro runs, as it is right now, I get my list of data. If I run the macro again, excel duplicates the data and I then have double the information.

Each day new information is added to a different sheet in the book. I want excel to add the new information to the existing information in my summary "sheet1." That is why I was saying "clearing the data" I don't want duplicate information but do want the new info added.

If an event is needed on sheet 1 to automatically run the macro, that would be ok. If it can be done without any input from me, that would be better.

 

[L. Howard]

I'm thinking this is your best bet

It clears columns A and B of sheet 1 and then brings the data from the other sheets. So, old data on sheet 1 gone, current data from the other sheets is posted.

After further thought the change event idea does not seem to be a good idea. I suggest you take a Forms button, make it small and place it somewhere easy to get to and assign this macro to it. A simple click of the button and the old data is gone and all 30 sheets new data is posted.

Will that work for you?

Also, note that you will need to either re-enter all your sheets into the array or do a copy and paste of that line from your existing code. See the array sheets in red. These need to be your sheet names.

Howard

Option Explicit

Sub ManyToOneCopy()
Dim c As Range
Dim i As Long
Dim MyArr As Variant
Dim varOut As Variant
Dim varOut1 As Variant
Dim lr As Long
Dim rngB As Range
Dim lrCl As Long

lrCl = Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Sheet1").Range("A1:B" & lrCl).ClearContents

MyArr = Array("Sheet2", "Sheet3", "Sheet4")
 '/ Add sheet names as required, order can vary if you want certain sheet to post before others.
              
Application.ScreenUpdating = False

For i = LBound(MyArr) To UBound(MyArr)
   
   With Sheets(MyArr(i))
      Set rngB = .Range("E13:E20")

      For Each c In rngB
         If c > 0 Then
            varOut = c.Offset(, -4)
            Sheets("Sheet1").Range("A" & Rows.Count) _
            .End(xlUp)(2) = varOut
            varOut1 = c
            Sheets("Sheet1").Range("B" & Rows.Count) _
            .End(xlUp)(2) = varOut1
         End If
      Next 'c

  End With
Next 'i

Application.ScreenUpdating = True
End Sub

 

[Darth_Sullivan]

I'm thinking this is your best bet

It clears columns A and B of sheet 1 and then brings the data from the other sheets. So, old data on sheet 1 gone, current data from the other sheets is posted.

After further thought the change event idea does not seem to be a good idea. I suggest you take a Forms button, make it small and place it somewhere easy to get to and assign this macro to it. A simple click of the button and the old data is gone and all 30 sheets new data is posted.

Will that work for you?

Also, note that you will need to either re-enter all your sheets into the array or do a copy and paste of that line from your existing code. See the array sheets in red. These need to be your sheet names.

Howard

Option Explicit

Sub ManyToOneCopy()
Dim c As Range
Dim i As Long
Dim MyArr As Variant
Dim varOut As Variant
Dim varOut1 As Variant
Dim lr As Long
Dim rngB As Range
Dim lrCl As Long

lrCl = Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Sheet1").Range("A1:B" & lrCl).ClearContents

MyArr = Array("Sheet2", "Sheet3", "Sheet4")
 '/ Add sheet names as required, order can vary if you want certain sheet to post before others.
              
Application.ScreenUpdating = False

For i = LBound(MyArr) To UBound(MyArr)
   
   With Sheets(MyArr(i))
      Set rngB = .Range("E13:E20")

      For Each c In rngB
         If c > 0 Then
            varOut = c.Offset(, -4)
            Sheets("Sheet1").Range("A" & Rows.Count) _
            .End(xlUp)(2) = varOut
            varOut1 = c
            Sheets("Sheet1").Range("B" & Rows.Count) _
            .End(xlUp)(2) = varOut1
         End If
      Next 'c

  End With
Next 'i

Application.ScreenUpdating = True
End Sub

Works great, looks great! Thanks again Howard!

 

[L. Howard]

You're welcome. Thanks for the feedback.

Regards,
Howard