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]

I'm not sure I will be able to offer a solution, but if you have multiple instances of your lookup value in the lookup column, then VLOOKUP is out the window. It only returns the from first match it finds. Any others below the first are ignored.

Secondly is the merged cells. I have neither the patience nor knowledge to work with merged cell where formulas, code and other worksheet functions are involved. Merged cells are fine elsewhere on the sheet to "make pretty with the sheet" but not in the working parts of a sheet.

You might consider Format Cells > Alignment > Center across selection as an alternative. (I assume 2002 has that feature)

It would also help if you could post a sample of a few rows of data along with some sample results you are looking for.

I think you can put borders around your data on your sheet and then copy and paste it in your post.

Or a link to a sample workbook by way of Drop Box (which I use) and there are others as well.

Regards,
Howard

 

[Darth_Sullivan]

I think you can put borders around your data on your sheet and then copy and paste it in your post.

Or a link to a sample workbook by way of Drop Box (which I use) and there are others as well.

Regards,
Howard

Please forgive me. I have looked through the FAQ and cannot figure out how to do either of these.

I do not have the permissions to install adobe flash, if that is the problem.

The merged cells are, of course, cosmetic. All the data gets input by another person, and is the reason for the cosmetics. Also, the workbook I am working with was already in place and I am trying to merely add a summary sheet to the existing workbook, rather than try to redesign the whole thing.

My merged cells that I want to be searched are from row 14 to row 20. Many of those cells may be blank, but the ones that aren't may not be the same row from sheet to sheet and may have multiple occurrences of the requested lookup value.

Another possible fix to my problem could be achieved if on the summary page, I could have excel find all values in E14:E20 that are >0 and return the value listed in the merged cells(column A & B) into the summary sheet. If excel can automatically add the new entries as each day passed, then I would have a list of all the relevant data. If I can get to that point then I could probably just use a sumif function to pull out the requested data from that point?

Sorry if I'm not making enough sense. I'm trying to do what I can with the tools I have available.

If it helps, my workbook is used for tracking the length of poker games in my poker room. Cells E14:E20 contain the length of time a game lasted. 2 cells are merged in each row from row 14 through row 20. A14 and B14 are one cell. In those cells, my worker lists the game that was played, "2/5 NL." Each sheet in the workbook represents one day of the month. Today being the 23rd, he will use the sheet named "Day 23." There are several rows as some days have more or less games than others. I want to be able to pull the information from this to find the average length of time a specific game type lasts so I can compare different game types...

Is there another way I can give any more needed info?

*edit Thanks for the quick response

 

[L. Howard]

I am at a loss as what to suggest, sorry.

Regards,
Howard

 

[Darth_Sullivan]

I am at a loss as what to suggest, sorry.

Regards,
Howard

Maybe a simpler question you might be able to answer...

How can I ask excel to find all values in column E across 31 different sheets that are >0 and return the value of a cell from the same row from column A?

If this is possible, I think I can figure out the rest of my problem from there...

Again, thanks for the quick response, and thank you for taking the time to help me.

 

[L. Howard]

Try this first code in the sheet where you want the data brought to. That sheet should be the first sheet in the workbook as the code starts with the second sheet and goes to all the ensuing sheets in order.

Although it is pretty generic, AlphaFrog offered it up in response to post I made trying to get a fix on the second code below.

This first one should be more easily adapted to your workbook than the second.

Option Explicit

Sub ManyToOneCopy2()
Dim c As Range
Dim i As Long
              
Application.ScreenUpdating = False
    
For i = 2 To Sheets.Count
   With Sheets(i)
      For Each c In .Range("E1", .Cells(.Rows.Count, 5).End(xlUp))
         If c > 0 Then
            Sheets("Sheet1").Range("A" & Rows.Count) _
                .End(xlUp)(2) = c.Offset(, -4)
         End If
     Next 'c
    
  End With
Next 'i
    
Application.ScreenUpdating = True
End Sub

This one requires a list of all the sheet names to be in the Array. However, once they are there it works quite well.

Option Explicit

Sub ManyToOneCopy1()
Dim c As Range
Dim i As Long
Dim MyArr As Variant
Dim varOut As Variant
Dim lr As Long
Dim rngB As Range

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 MyArr(i)
   With Sheets(MyArr(i))
      lr = .Cells(.Rows.Count, 5).End(xlUp).Row
      Set rngB = .Range("E1:E" & lr)

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

  End With
Next 'i

Application.ScreenUpdating = True
End Sub

Regards,
Howard

 

[Darth_Sullivan]

This one requires a list of all the sheet names to be in the Array. However, once they are there it works quite well.

Option Explicit

Sub ManyToOneCopy1()
Dim c As Range
Dim i As Long
Dim MyArr As Variant
Dim varOut As Variant
Dim lr As Long
Dim rngB As Range

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 MyArr(i)
   With Sheets(MyArr(i))
      lr = .Cells(.Rows.Count, 5).End(xlUp).Row
      Set rngB = .Range("E1:E" & lr)

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

  End With
Next 'i

Application.ScreenUpdating = True
End Sub

Regards,
Howard

I input this into my worksheet and is working to some extent. I added all of my sheets into the array and the code does create a list in my summary sheet. What I want to add to this is the information from column E also. Also, I only need the code to search through E13:E20 of each sheet and not the whole column as it currently does. I tried changing the part of the code that says:

Set rngB = .Range("E1:E" & lr)

to say:

Set rngB = .Range("E13:E20" & lr)

as I thought that would solve that problem. I am not well versed yet in programming though, so I am unsure how to solve these problems. Can you help me to solve these issues? Otherwise, this is working great and doing exactly what I need.

Thank you for any and all help.

 

[Aladin Akyurek]

Maybe a simpler question you might be able to answer...

How can I ask excel to find all values in column E across 31 different sheets that are >0 and return the value of a cell from the same row from column A?

If this is possible, I think I can figure out the rest of my problem from there...

Again, thanks for the quick response, and thank you for taking the time to help me.

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

 

[L. Howard]

This should work.

In your Dim statements add:

Dim varOut1 As Variant


Swap out either of the snippets below that suit you.

I think I got all the combo and wording correct as to what the various snippets do.
You've got two versions of copied data in a single cell in col A sheet 1.

And two versions of copied data either in A or B in col A & B of sheet 1.

This will give you what is in column E, a space and what is in column A

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

This will give you what is in column A, a space and what is in column E

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

This will give you what is in column A to Column A on Sheet 1, and what is in column E to Column B on Sheet 1.

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

This will give you what is in column A to Column B on Sheet 1, and what is in column E to Column A on Sheet 1.

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

Regards,
Howard

 

[Darth_Sullivan]

This will give you what is in column A to Column A on Sheet 1, and what is in column E to Column B on Sheet 1.

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

Regards,
Howard

This does work perfectly! Well, almost perfectly. The last thing I need to change regarding this that I only need the values that met the criteria that are between rows 13 and 20. Is it possible to add a condition to only give me what fell between those rows?

Again, thanks for the help. This has helped immensely and sorry for the late reply as I it's been a busy week at work.