VBA Complex Lookup

A

anthonyexcel

Active Member
Joined
Jun 10, 2011
Messages
258
Office Version
  1. 365
Platform
  1. Windows
The Data that I have on sheet one needs to bring back data from sheet 2 based on ID Number and day (which will bring back the school associated with the ID and Day. Thanks in advance Anthony.


******** ******************** ************************************************************************><CENTER>
Microsoft Excel - Breakdown.xlsm___Running: 14.0 : OS = Windows XP

<TBODY>
</TBODY>
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout<FORM name=formCb755237><INPUT value="Copy Formula" type=button name=btCb873980 *******='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);'></FORM>

<TBODY>
</TBODY>
<SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705><OPTION selected value="">A1</OPTION></SELECT>=<INPUT value=ID_Number size=80 name=txbFb426622>

<TBODY>
<FORM name=formFb078704>
</FORM>
</TBODY>
<CENTER>A</CENTER><CENTER>B</CENTER><CENTER>C</CENTER><CENTER>D</CENTER>
<CENTER>1</CENTER>ID_NumberDays
<CENTER>2</CENTER>227Thu
<CENTER>3</CENTER>227Tue
<CENTER>4</CENTER>233Fri
<CENTER>5</CENTER>3261Tue
<CENTER>6</CENTER>3261Wed
<CENTER>7</CENTER>3261Thu
<CENTER>8</CENTER>103Fri, Mon
<CENTER>9</CENTER>103Tue
<CENTER>10</CENTER>103Wed, Thu, Fri, Tue, Wed
<CENTER>11</CENTER>103Thu
<CENTER>12</CENTER>103Fri, Mon, Tue, Wed, Thu, Fri
<CENTER>13</CENTER>103Mon, Tue, Wed
<CENTER>14</CENTER>303Fri
<CENTER>15</CENTER>303Fri, Mon, Tue, Wed
<CENTER>16</CENTER>303Fri
<CENTER>17</CENTER>303Mon
Sheet1

<TBODY>
</TBODY>

<TBODY>
</TBODY>

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>


This is what I have on sheet2

******** ******************** ************************************************************************><CENTER>
Microsoft Excel - Breakdown.xlsm___Running: 14.0 : OS = Windows XP

<TBODY>
</TBODY>
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout<FORM name=formCb605117><INPUT value="Copy Formula" type=button name=btCb942116 *******='window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);'></FORM>

<TBODY>
</TBODY>
<SELECT onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name=sltNb447362><OPTION selected value=Harding>A1</OPTION></SELECT>=<INPUT value=ID size=80 name=txbFb150492>

<TBODY>
<FORM name=formFb202339>
</FORM>
</TBODY>
<CENTER>A</CENTER><CENTER>B</CENTER><CENTER>C</CENTER><CENTER>D</CENTER><CENTER>E</CENTER><CENTER>F</CENTER>
<CENTER>1</CENTER>IDMonTueWedThuFri
<CENTER>2</CENTER>227JohnsonJohnsonJohnsonJohnsonJohnson
<CENTER>3</CENTER>233Roosevelt, WashingtonRoosevelt, WashingtonLincoln, Washington, TylerLincoln, WashingtonRoosevelt, Washington
<CENTER>4</CENTER>3261KennedyCoolidgeCoolidgeKennedyKennedy
<CENTER>5</CENTER>257BarnumBarnumBarnumBarnum
<CENTER>6</CENTER>2135MadisonMadisonMonroe, MadisonMadison
<CENTER>7</CENTER>103Harrison, TaylorTyler, FillmoreMadison, McKinley, WilsonMadison, McKinley, WilsonGarfield
<CENTER>8</CENTER>303HardingHardingHardingHardingHarding
Sheet2

<TBODY>
</TBODY>

<TBODY>
</TBODY>

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>

This is what I would like the output to look like:

******** ******************** ************************************************************************><CENTER>
Microsoft Excel - Breakdown.xlsm___Running: 14.0 : OS = Windows XP

<TBODY>
</TBODY>
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout<FORM name=formCb059465><INPUT value="Copy Formula" type=button name=btCb290334 *******='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);'></FORM>

<TBODY>
</TBODY>
<SELECT onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name=sltNb142651><OPTION selected value="">A1</OPTION></SELECT>=<INPUT value=ID_Number size=80 name=txbFb965317>

<TBODY>
<FORM name=formFb543420>
</FORM>
</TBODY>
<CENTER>A</CENTER><CENTER>B</CENTER><CENTER>C</CENTER><CENTER>D</CENTER>
<CENTER>1</CENTER>ID_NumberDays
<CENTER>2</CENTER>227ThuJohnson
<CENTER>3</CENTER>227TueJohnson
<CENTER>4</CENTER>233FriRoosevelt, Washington
<CENTER>5</CENTER>3261TueCoolidge
<CENTER>6</CENTER>3261WedCoolidge
<CENTER>7</CENTER>3261ThuKennedy
<CENTER>8</CENTER>103Fri, MonGarfield, Harrison, Taylor
<CENTER>9</CENTER>103TueTyler, Fillmore
<CENTER>10</CENTER>103Wed, Thu, Fri, Tue, WedMadison, McKinley, Wilson, Madison, McKinley, Wilson, Garfield, Tyler, Fillmore, Madison, McKinley, Wilson
<CENTER>11</CENTER>103ThuMadison, McKinley, Wilson
<CENTER>12</CENTER>103Fri, Mon, Tue, Wed, Thu, FriGarfield, Harrison, Taylor, Tyler, Fillmore, Madison, McKinley, Wilson, Madison, McKinley, Wilson, Garfield
<CENTER>13</CENTER>103Mon, Tue, WedHarrison, Taylor, Tyler, Fillmore, Madison, McKinley, Wilson
<CENTER>14</CENTER>303FriHarding
<CENTER>15</CENTER>303Fri, Mon, Tue, WedHarding, Harding, Harding, Harding
<CENTER>16</CENTER>303FriHarding
<CENTER>17</CENTER>303MonHarding
Sheet3

<TBODY>
</TBODY>

<TBODY>
</TBODY>

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
This should work. Copy and paste it to your standard code module.
Code: 
Sub getSchool2()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range, rw As Range, col As Range
Dim i As Long, dys As Variant
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit Sheet name
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A2:a" & lr)
    For Each c In rng
        Set rw = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not rw Is Nothing Then
                dys = Split(c.Offset(0, 1), ", ")
                For i = LBound(dys) To UBound(dys)
                    Set col = sh2.Rows(1).Find(dys(i), , xlValues, xlPart, MatchCase:=False)
                        If Not col Is Nothing Then
                            If i = LBound(dys) Then
                                c.Offset(0, 2) = sh2.Cells(rw.Row, col.Column).Value
                            Else
                                c.Offset(0, 2) = c.Offset(0, 2).Value & ", " & sh2.Cells(rw.Row, col.Column).Value
                            End If
                            Set col = Nothing
                        End If
                Next
            End If
    Next
End Sub
 
Upvote 0
JLGWhiz,

Thank you so much for your help. Worked perfectly! Thank you again, much appreciated!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
EXTRAPOLATE DATA FROM A TABLE!!!
Replies
6
Views
995
JamesCanale
JamesCanale
S
  • Solved
using advanced filter with 2 criteria one having a list & the other having text or no text
Replies
7
Views
647
RoryA
RoryA
markkeith
  • Solved
Function or Formula to get a value for each cell from multiple logical test.
Replies
3
Views
551
markkeith
markkeith
M
  • Question
VBA - return value in different columns upon a criteria
Replies
6
Views
648
DanteAmor
DanteAmor
B
  • Question
Matching Values in Tables
Replies
4
Views
511
sandy666
sandy666

Forum statistics

Threads
1,215,064
Messages
6,122,942
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top