Comparing Columns Between Worksheets to Find a Value

J

jimmynora

New Member
Joined
Oct 20, 2010
Messages
27
(sample file: http://www.wikiupload.com/M6PBA41ANKQPV3G )

Hi,

I need to fill in the empty cells in Column P (sheet1). This value(s) is to be found in Sheet2 Column A.

The procedure for the macro:
- Run only for empty cells in Sheet1 Column P (skips cells with data)
- Compare the data in the 3 columns in Sheet1 (E, F and H) with the data in the 3 Columns of Sheet2 (E,H, and F) respectively.
- If all the 3 columns match each other then copy the value from the respective cell in Column A Sheet2 and paste it to the respective cell in Column P Sheet1.
- In case of multiple matches, all the matching values found in Column A Sheet2 should be pasted in Column P Sheet1.

I hope you can help me with this. Either via VB Code (macro) or an Excel procedure.

Thank You,
JN



Uploaded with ImageShack.us


Uploaded with ImageShack.us


Uploaded with ImageShack.us
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
I used this code in cell R2 Sheet1
=MATCH(1, (E2=Sheet2!$E$5:$E$12) * (F2=Sheet2!$H$5:$H$12) * (H2=Sheet2!$F5$:$F$12), 0)

Enter the formula by pressing Ctrl+Shift+Enter. Copy down.

Then I entered the formula in cell S2 Sheet 1
=INDEX($A5$:$A$12, R2)

Copy Down.

This only gives me 1 instance of the matching value. I would like to get all the matching values and not only the first match. How would that be possible ?
 
Upvote 0
I used this code in cell R2 Sheet1
=MATCH(1, (E2=Sheet2!$E$5:$E$12) * (F2=Sheet2!$H$5:$H$12) * (H2=Sheet2!$F$5:$F$12), 0)

Enter the formula by pressing Ctrl+Shift+Enter. Copy down.

Then I entered the formula in cell S2 Sheet 1
=INDEX($A$5:$A$12, R2)

Copy Down.

This only gives me 1 instance of the matching value. I would like to get all the matching values and not only the first match. How would that be possible ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
256
B-radK
B
P
  • Question
Automating copying of columns within same sheet in excel using VBA.
Replies
1
Views
171
MountainFog
M
R
  • Question
VBA Check if value in Sheet1 exists in Sheet2 then copy specific cells from Sheet2 into Sheet1
2
Replies
13
Views
774
dermie_72
D
E
  • Solved
Summing Values
Replies
3
Views
244
LazyBug
LazyBug
G
  • Solved
Stop excel macro from running if there is a blank cell in a column
Replies
5
Views
181
RobP
R

Forum statistics

Threads
1,215,455
Messages
6,124,935
Members
449,195
Latest member
Stevenciu

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