Search and Match and show the result

Vishaal

Well-known Member
Joined
Mar 16, 2019
Messages
530
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Many thanks DanteAmor Ji

Its working for me and saved my lots of time

Thanks sir ji
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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