Search and Match and show the result

Vishaal

Active Member
Hi All,

Thanks in advance,

We want to check and match from two sheet as per given sheets

First Sheet from where we will compare and match

Excel 2010 32 bit
A
B
C
D
E
F
G
H
I
J
K
1
S.No.​
Age Group​
Danial​
Chrix​
Rocky​
Ricky​
Danial​
Chrix​
Rocky​
Ricky​
2
Ronaldo​
Pamela​
Donald​
messy​
Ronaldo​
Pamela​
Donald​
messy​
3
4
1​
20​
Yes​
Na​
Yes​
Na​
5
2​
30​
Na​
Na​
Na​
Na​
6
3​
40​
Na​
Na​
Na​
Na​
2​
3​
7
4​
50​
Yes​
Yes​
Na​
Na​
4​
8
5​
60​
Yes​
Na​
Na​
Yes​
1​
9
6​
70​
Na​
Yes​
Na​
Yes​
1​
5​
Sheet: Sheet1


Second Sheet from where we match and show the data

Excel 2010 32 bit
A
B
C
D
E
1
S.No.​
Danial​
Chrix​
Rocky​
Ricky​
2
Ronaldo​
Pamela​
Donald​
messy​
3
4
1​
5
2​
6
3​
Yes​
7
4​
Yes​
Yes​
8
5​
Yes​
Yes​
9
6​
Sheet: Sheet1


We will check and match if Range H4:H9 will match Range A4:A9 and if there is Yes in B4:B9 then it will copy the S.No. and heading and paste in first sheet coloumn,

Result sheet

Excel 2010 32 bit
A
B
C
D
E
F
G
H
I
J
K
1
S.No.​
Age Group​
Danial​
Chrix​
Rocky​
Ricky​
Danial​
Chrix​
Rocky​
Ricky​
2
Ronaldo​
Pamela​
Donald​
messy​
Ronaldo​
Pamela​
Donald​
messy​
3
4
1​
20​
Yes​
Na​
Yes​
Na​
5
2​
30​
Na​
Na​
Na​
Na​
6
3​
40​
Na​
Na​
Na​
Na​
2​
3​
7
4​
50​
Yes​
Yes​
Na​
Na​
4​
8
5​
60​
Yes​
Na​
Na​
Yes​
1​
9
6​
70​
Na​
Yes​
Na​
Yes​
1​
5​
10
11
12
5​
4​
13
Rocky​
Ricky​
14
Donald​
messy​
Sheet: Sheet1

Help pls
 

DanteAmor

Well-known Member
I have a couple of doubts.


The names in the headings are in two rows?
Your example result "5 Rocky Donald" is not correct, since 5 (cell J9) is in row 6 (row 9).


You can comment
 

Vishaal

Active Member
Thanks for giving the time DanteAmor Ji

1. Yes, the names in headings are in two row
2. "5" is a number which have "Yes" in second data

I m checking the following

If H4 value from first data is match with second data A4 and B4 have "Yes" - copy the headings and matched number and paste in first data

If H5 value from first data is match with second data A5 and B5 have "Yes" - copy the headings and matched number and paste in first data

If H6 value from first data is match with second data A6 and B6 have "Yes" - copy the headings and matched number and paste in first data

For coloumn I
If I4 value from first data is match with second data A4 and C4 have "Yes" - copy the headings and matched number and paste in first data

If I5 value from first data is match with second data A5 and C5 have "Yes" - copy the headings and matched number and paste in first data

Coloumn J
If J4 value from first data is match with second data A4 and D4 have "Yes" - copy the headings and matched number and paste in first data

Pls confirm if all the points are clear
 

DanteAmor

Well-known Member
I adjusted the code to put the resulting numbers below the headings in case of having several results.

Sheet1
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:47.52px;" /><col style="width:86.5px;" /><col style="width:67.49px;" /><col style="width:61.78px;" /><col style="width:58.93px;" /><col style="width:55.13px;" /><col style="width:76.04px;" /><col style="width:67.49px;" /><col style="width:61.78px;" /><col style="width:58.93px;" /><col style="width:55.13px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">S.No.</td><td style="background-color:#ffff00; ">Age Group</td><td style="background-color:#ffff00; ">Danial</td><td style="background-color:#ffff00; ">Chrix</td><td style="background-color:#ffff00; ">Rocky</td><td style="background-color:#ffff00; ">Ricky</td><td > </td><td style="background-color:#ffff00; ">Danial</td><td style="background-color:#ffff00; ">Chrix</td><td style="background-color:#ffff00; ">Rocky</td><td style="background-color:#ffff00; ">Ricky</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; "> </td><td style="background-color:#ffff00; "> </td><td style="background-color:#ffff00; ">Ronaldo</td><td style="background-color:#ffff00; ">Pamela</td><td style="background-color:#ffff00; ">Donald</td><td style="background-color:#ffff00; ">messy</td><td > </td><td style="background-color:#ffff00; ">Ronaldo</td><td style="background-color:#ffff00; ">Pamela</td><td style="background-color:#ffff00; ">Donald</td><td style="background-color:#ffff00; ">messy</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">1</td><td style="text-align:right; ">20</td><td >Yes</td><td >Na</td><td >Yes</td><td >Na</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">2</td><td style="text-align:right; ">30</td><td >Na</td><td >Na</td><td >Na</td><td >Na</td><td > </td><td > </td><td > </td><td style="text-align:right; ">3</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">3</td><td style="text-align:right; ">40</td><td >Na</td><td >Na</td><td >Na</td><td >Na</td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">4</td><td style="text-align:right; ">50</td><td >Yes</td><td >Yes</td><td >Na</td><td >Na</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">5</td><td style="text-align:right; ">60</td><td >Yes</td><td >Na</td><td >Na</td><td >Yes</td><td > </td><td > </td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">6</td><td style="text-align:right; ">70</td><td >Na</td><td >Yes</td><td >Na</td><td >Yes</td><td > </td><td style="text-align:right; ">1</td><td > </td><td style="text-align:right; ">5</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Rocky</td><td >Ricky</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Donald</td><td >messy</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">5</td><td > </td></tr></table>
Sheet2
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:38.02px;" /><col style="width:55.13px;" /><col style="width:50.38px;" /><col style="width:48.48px;" /><col style="width:43.72px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">S.No.</td><td style="background-color:#ffff00; ">Danial</td><td style="background-color:#ffff00; ">Chrix</td><td style="background-color:#ffff00; ">Rocky</td><td style="background-color:#ffff00; ">Ricky</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; "> </td><td style="background-color:#ffff00; ">Ronaldo</td><td style="background-color:#ffff00; ">Pamela</td><td style="background-color:#ffff00; ">Donald</td><td style="background-color:#ffff00; ">messy</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">2</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">3</td><td > </td><td > </td><td >Yes</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">4</td><td >Yes</td><td > </td><td > </td><td >Yes</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">5</td><td > </td><td >Yes</td><td >Yes</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">6</td><td > </td><td > </td><td > </td><td > </td></tr></table>

Try the code and give me your opinion.

Code:
Sub Search_and_Match()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim lr As Long, lc As Long, col As Long, lr2 As Long
  Dim c As Range, f As Range, r As Range
  '
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  lr = sh1.Range("A" & Rows.Count).End(xlUp).Row
  lc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
  Set r = sh1.Range("H4", sh1.Cells(lr, lc))
  r.Offset(r.Rows.Count + 2).ClearContents
  For Each c In r
    If c.Value <> "" Then
      col = c.Column - r.Cells(1, 1).Column + 1
      Set f = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
      If Not f Is Nothing Then
        If f.Offset(, col).Value = "Yes" Then
          sh1.Cells(lr + 3, c.Column).Resize(2).Value = sh1.Cells(1, c.Column).Resize(2).Value
          lr2 = sh1.Cells(Rows.Count, c.Column).End(xlUp).Row + 1
          sh1.Cells(lr2, c.Column).Value = c.Value
        End If
      End If
    End If
  Next
End Sub
 

Vishaal

Active Member
Many thanks DanteAmor Ji

Its working for me and saved my lots of time

Thanks sir ji
 

Vishaal

Active Member
Hi Sir,

We are using the code and want some modification, like

1. if we need to add or remove header data from two rows to one rows or six row, how can we do that
2. how can we fix that the result will always show from after five or six rows from last data

help pls
 

Vishaal

Active Member
Hi DanteAmor Ji,

if we are using your code with the sample data its working fine but when we are using with the following sheet its not working, we have also check it with making some changes in your code

Excel 2010 32 bit
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
1
Danial​
Chrix​
Rocky​
Ricky​
log​
ghun​
Danial​
Chrix​
Rocky​
Ricky​
log​
ghun​
2
Ronaldo​
Pamela​
Donald​
messy​
sanju​
safi​
Ronaldo​
Pamela​
Donald​
messy​
sanju​
safi​
3
shika​
rghu​
randy​
john​
vijju​
rodi​
shika​
rghu​
randy​
john​
vijju​
rodi​
4
lovely​
rick​
flair​
prave​
archi​
tina​
lovely​
rick​
flair​
prave​
archi​
tina​
5
rinku​
mone​
rashmi​
peter​
novit​
emli​
rinku​
mone​
rashmi​
peter​
novit​
emli​
6
7
8
1​
9
2​
17000​
700​
Na​
Na​
Na​
Na​
Na​
Na​
1​
1​
1​
1​
10
3​
83000​
300​
Na​
Na​
2​
2​
11
4​
92000​
200​
12
5​
42000​
200​
Na​
1​
13
6​
69000​
900​
Na​
Na​
1​
1​
14
7​
93000​
300​
Na​
15
8​
31000​
100​
Na​
Na​
Na​
16
9​
56000​
600​
Na​
Na​
Na​
Na​
Na​
1​
17
10​
58000​
800​
Na​
Na​
Na​
Na​
2​
4​
3​
3​
Sheet: Sheet1

Second Sheet for search and match

Excel 2010 32 bit
A
B
C
D
E
F
G
1
Danial​
Chrix​
Rocky​
Ricky​
log​
ghun​
2
Ronaldo​
Pamela​
Donald​
messy​
sanju​
safi​
3
shika​
rghu​
randy​
john​
vijju​
rodi​
4
lovely​
rick​
flair​
prave​
archi​
tina​
5
rinku​
mone​
rashmi​
peter​
novit​
emli​
6
7
8
1​
9
2​
Yes​
10
3​
Yes​
11
4​
Yes​
12
5​
Yes​
13
6​
Yes​
Yes​
14
7​
15
8​
Yes​
16
9​
17
10​
Yes​
18
11​
Yes​
Sheet: Sheet2

Result Sheet

Excel 2010 32 bit
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
1
Danial​
Chrix​
Rocky​
Ricky​
log​
ghun​
Danial​
Chrix​
Rocky​
Ricky​
log​
ghun​
2
Ronaldo​
Pamela​
Donald​
messy​
sanju​
safi​
Ronaldo​
Pamela​
Donald​
messy​
sanju​
safi​
3
shika​
rghu​
randy​
john​
vijju​
rodi​
shika​
rghu​
randy​
john​
vijju​
rodi​
4
lovely​
rick​
flair​
prave​
archi​
tina​
lovely​
rick​
flair​
prave​
archi​
tina​
5
rinku​
mone​
rashmi​
peter​
novit​
emli​
rinku​
mone​
rashmi​
peter​
novit​
emli​
6
7
8
1​
9
2​
17000​
700​
Na​
Na​
Na​
Na​
Na​
Na​
1​
1​
1​
1​
10
3​
83000​
300​
Na​
Na​
2​
2​
11
4​
92000​
200​
12
5​
42000​
200​
Na​
1​
13
6​
69000​
900​
Na​
Na​
1​
1​
14
7​
93000​
300​
Na​
15
8​
31000​
100​
Na​
Na​
Na​
16
9​
56000​
600​
Na​
Na​
Na​
Na​
Na​
1​
17
10​
58000​
800​
Na​
Na​
Na​
Na​
2​
4​
3​
3​
18
19
20
4​
3​
21
Chrix​
ghun​
22
Pamela​
safi​
23
rghu​
rodi​
24
rick​
tina​
25
mone​
emli​
Sheet: Sheet1

in this sheet we will check only last filled row data to second sheet data
i mean k9:p9
when we add data on 10th row then we will check only k10:p10, we will not check any other data
When we add data on 11th row then we will check only k11:p11, we will not check any other data
When we add data on 12th row then we will check only k12:p1, we will not check any other data

all the points are same as previous question, except the above point

help pls
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top