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

Top