How to lookup and get all the adjacent cells simultaneously

[Sarath Karanam]

Hi,

Can someone please let me know how to vlookup using VBA and meanwhile get all the adjacent cells if the condition matches.

Example:

Sheet 1:

Location	Time Standard	Code 1	Code 2	Code 3	Code 4	Code 5
India
USA

<tbody>
</tbody>

Sheet 2:

Location	Time Standard	Code 1	Code 2	Code 3	Code 4	Code 5
England	GMT	a	b	a+b	a/b	a*b
India	IST	c	d	c+d	c/d	c*d
USA	EST	e	f	e+f	e/f	e*f
Japan	JST	g	h	g+h	g/h	g*h

<tbody>
</tbody>

How do I vlookup data from "Sheet 2" to "Sheet 1" and if the match is found, how can I get the adjacent cells without a need to vlookup for them too.

Here, the output should be:

Location	Time Standard	Code 1	Code 2	Code 3	Code 4	Code 5
India	IST	c	d	c+d	c/d	c*d
USA	EST	e	f	e+f	e/f	e*f

<tbody>
</tbody>

Once the code finds that "IST" matches "India", it should copy the next 5 cells of "Sheet 2" to "Sheet 1" in the same order.

Thank you.


Sarath Karanam

 

[James006]

Hi,

Is there a specific reason for a VBA solution ?

It seems that an Advanced Filter with named ranges could solve this problem

HTH

 

[madforgolf]

This should be a start tho its a forumla not VBA.

Sheet1

*	B	C	D	E	F	G	H	I
3	*	*	*	*	*	*	*	*
4	*	Location	Time Standard	Code 1	Code 2	Code 3	Code 4	Code 5
5	*	England	GMT	a	b	a+b	a/b	a*b
6	*	India	IST	c	d	c+d	c/d	c*d
7	*	USA	EST	e	f	e+f	e/f	e*f
8	*	Japan	JST	g	h	g+h	g/h	g*h
9	*	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*
12	gmt	*	a	b	a+b	a/b	a*b	*
13	IST	*	c	d	c+d	c/d	c*d	*
14	EST	*	e	f	e+f	e/f	e*f	*
15	JST	*	g	h	g+h	g/h	g*h	*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D12 =VLOOKUP($B12,$D$5:$I$8,2,FALSE) E12 =VLOOKUP($B12,$D$5:$I$8,3,FALSE) F12 =VLOOKUP($B12,$D$5:$I$8,4,FALSE) G12 =VLOOKUP($B12,$D$5:$I$8,5,FALSE) H12 =VLOOKUP($B12,$D$5:$I$8,6,FALSE) D13 =VLOOKUP($B13,$D$5:$I$8,2,FALSE) E13 =VLOOKUP($B13,$D$5:$I$8,3,FALSE) F13 =VLOOKUP($B13,$D$5:$I$8,4,FALSE) G13 =VLOOKUP($B13,$D$5:$I$8,5,FALSE) H13 =VLOOKUP($B13,$D$5:$I$8,6,FALSE) D14 =VLOOKUP($B14,$D$5:$I$8,2,FALSE) E14 =VLOOKUP($B14,$D$5:$I$8,3,FALSE) F14 =VLOOKUP($B14,$D$5:$I$8,4,FALSE) G14 =VLOOKUP($B14,$D$5:$I$8,5,FALSE) H14 =VLOOKUP($B14,$D$5:$I$8,6,FALSE) D15 =VLOOKUP($B15,$D$5:$I$8,2,FALSE) E15 =VLOOKUP($B15,$D$5:$I$8,3,FALSE) F15 =VLOOKUP($B15,$D$5:$I$8,4,FALSE) G15 =VLOOKUP($B15,$D$5:$I$8,5,FALSE) H15 =VLOOKUP($B15,$D$5:$I$8,6,FALSE) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[Sarath Karanam]

Hi James & Madforgolf,

Thank you for responding.

I need this to handle huge data quite often.

VBA reduces the time for this and also the file size would be small.

Also, if I use VBA I don't have to worry about clearing the entire sheet


Sarath Karanam

 

[James006]

Hi Sarath,

At this stage, my recommendation is for you :

1. To create Named ranges for your data, your criteria, and your output area

2. Turn on your macro recorder, and go through the Advanced Filter process

You will get automatically the VBA version of what you are trying to build.

HTH

 

[Sarath Karanam]

Thank you James,

I'll try this.

Hi Sarath,

At this stage, my recommendation is for you :

1. To create Named ranges for your data, your criteria, and your output area

2. Turn on your macro recorder, and go through the Advanced Filter process

You will get automatically the VBA version of what you are trying to build.

HTH

 

[konew1]

On your summary sheet put =INDEX(Sheet2!$A$2:$G$5,MATCH(Sheet1!$A2,Sheet2!$A$2:$A$5),COLUMN($A$2)+COLUMNS($A$2:B2)-1) for the first data item you want returned (in your example India/TimeStd) and copy across and down.
This assumes the data on sheet2 starts in A1 with headings in row 1 and data in row2

 

[Rick Rothstein]

Give this macro a try...

Sub GetTimeZoneCodeData()
  Dim X As Long, Z As Long, LastRow1 As Long, LastRow2 As Long, WS1 As Worksheet, WS2 As Worksheet
  Set WS1 = Worksheets("Sheet1")
  Set WS2 = Worksheets("Sheet2")
  LastRow1 = WS1.Cells(Rows.Count, "A").End(xlUp).Row
  LastRow2 = WS2.Cells(Rows.Count, "A").End(xlUp).Row
  For X = 2 To LastRow1
    For Z = 2 To LastRow2
      If WS1.Cells(X, "A") & WS1.Cells(X, "B") = WS2.Cells(Z, "A") & WS2.Cells(Z, "B") Then
        WS2.Cells(Z, "C").Resize(, 5).Copy WS1.Cells(X, "C")
        Exit For
      End If
    Next
  Next
End Sub

 

[Sarath Karanam]

Hi Rick,

Thank you for the help.

I've tried this macro. Unfortunately, there's no result (Nothing's happening when I run the macro).

I've saved the file as Book1.xlsm.

Please let me know if I'm missing something.


Regards,
Sarath Karanam

Give this macro a try...

Sub GetTimeZoneCodeData()
  Dim X As Long, Z As Long, LastRow1 As Long, LastRow2 As Long, WS1 As Worksheet, WS2 As Worksheet
  Set WS1 = Worksheets("Sheet1")
  Set WS2 = Worksheets("Sheet2")
  LastRow1 = WS1.Cells(Rows.Count, "A").End(xlUp).Row
  LastRow2 = WS2.Cells(Rows.Count, "A").End(xlUp).Row
  For X = 2 To LastRow1
    For Z = 2 To LastRow2
      If WS1.Cells(X, "A") & WS1.Cells(X, "B") = WS2.Cells(Z, "A") & WS2.Cells(Z, "B") Then
        WS2.Cells(Z, "C").Resize(, 5).Copy WS1.Cells(X, "C")
        Exit For
      End If
    Next
  Next
End Sub

 

[Rick Rothstein]

Hi Rick,

Thank you for the help.

I've tried this macro. Unfortunately, there's no result (Nothing's happening when I run the macro).

I've saved the file as Book1.xlsm.

Please let me know if I'm missing something.

It is hard to know what might be wrong without my seeing your workbook directly. Can you send it to me? My email address is rickDOTnewsAtverizonDOTnet (replace the upper case letters with the symbols they spell out). Just so you know, I tested the code before I posted it and it worked on the same data that I tried to set up in accordance with the sample you posted.