search automation

[OCM]

Greetings,

Following is what I would like to automate:

Take a record from Workbook #1 column D and search if this record exists in Workbook #2 Column P.

If the record exists, then take the corresponding record from Column W in the same Workbook (workbook #2) and copy the record in Workbook #1 column H. If no match, then column H will be filled with n/a.

Thank you in advance,



OCM

 

[Andrzejek]

Can you substitute the word “Cell” or "Value from call” for your word “Record”?
Record to me is entire row in Excel


Have fun.

---- Andy

 

[kjv1611]

look at the Excel function, VLOOKUP. Try Excel help or Google for references.

Post back if you need more help.

 

[mintjulep]

Does workbook#2, column P contain a set of UNIQUE values?

 

[kjv1611]

by the way,

OCM,

Please take a look at the posting policies for the site. Remember to specify what solution(s) work best for you or that you find most helpful. Let the person know what it was, but also clickt he "thank so and so for this valuable post". That way others can know it was helpful when they're searching the fora.

Along that same thought, if you have the time, take a look at the fora you participate in. If you think you can have something to add that will help someone else with a situation you've worked through similarly in the past, pitch in and help out. If none of us were to help others, then none of us would get any help.

But on this thread, let us know how things go... communicate what you do, whether works or not, and ask questions as you progress.

 

[OCM]

Thanks everybody for your inputs.

My goal is to populate columns G and H in my Workbook1 with the cell content from Workbook2.

In workbook2 these columns are named different, but the cell contents are the same.

Following is what I tried:

=VLOOKUP(D3,'[Workbook2.xlsx]Sheet1'!$P$2:$W$400,8,FALSE)

=Vlookup(D3,'[Workbook2.xlsx]Sheet1'!P:W,8,FALSE)

I was getting #N/A, and when the a record exists, for some reason, the cell is filled with 0


Thanks,


OCM

 

[SkipVought]

So the value in D3 exists in the other wb in column P?

Are ALL the values in column D AND other wb col P, either ALL TEXT or ALL NUMBERS and not a mixture of TEXT & NUMBERS?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[OCM]

Thanks Skip,

Correct the value in column D, wb1 exists in column P, wb2.

I’m glad you asked, the values in column D and other column P are a mixture of TEXT & NUMBERS. Ex. Address1, Phone Number1 etc.



Regards,

OCM

 

[Gavona]

I was getting #N/A, and when the a record exists, for some reason, the cell is filled with 0

The behaviour you report is what I would expect if there were a blank cell in column W. If there are multiple matches in wb2, column P then the first occurrence will be matched.

Things I would try:
Copy the value from wb2 cell P2 to wb1 cell D3. You now know that you have a match and can observe what the formula is returning.

On the rows returning a zero try
=match(D3,'[Workbook2.xlsx]Sheet1'!P,false)
This will tell you which row is matched so you can investigate the values in column W.

Gavin

 

[OCM]

Thanks Gavona for your suggestions.
I've tried your suggestions & still get #N/A and 0. Is't possible to attach a strip version of my excel wb?



Regards,



OCM

 

[Gavona]

All I can think is to do some more detective work. The following is based on Excel 2003.

Focussing on an example where there is a match:
1. Can you post the formula,
2. The result,
3. The value in the cell it is looking up (eg D3)
4. The the value that you think should math (from column P) and the row number
5. The value that you think it should be returning (column W)

6. The result if you change the 8 in the formula to a 1.

7. The result of changing the formula to
=match(D3,'[Workbook2.xlsx]Sheet1'!P,false)
(substituting the appropriate cell ref for D3)
8. If this is a number then what is the value in that row, column p? (Should be same as in step 4)

9. The status of the linked workbook - Edit, Links, Check Status
10. If status is "Open" then that's OK. If Status is "OK" then Edit. Links, Open Source to open it. Is this the right workbook?!

11. Try this:
Copy the content of D3 (adjust for the row of interest).
Select column P in the other workbook.
Ctrl-F to start a Find. Make sure options are set to "Match entire cell contents".
Note which row is matched. Should be the same as steps 4 and 8.



Gavin

 

[OCM]

Thank you very much Gavin for your detail steps and help me resolve this.

Below is answer to some of your questions:

My formula:
=VLOOKUP(D3,[Workbook2.xlsx]Sheet1!$P$2:$W$400,8,FALSE)
The result= #N/A, when there is no match at all
result in H = 0 when there is a match in P but, corresponding cell W is empty and
finally, the result I was looking for: when a corresponding value exists in W, for P, it returns this value inside Column H as planned.
for the formula
=match(D3,'[Workbook2.xlsx]Sheet1'!P,false)
if there is a match no match for in corresponding W, result = 68, but if there is a match, this formula works as well.


Thanks again.


OCM

 

[Gavona]

finally, the result I was looking for: when a corresponding value exists in W, for P, it returns this value inside Column H as planned.

Glad you got it sorted.

Gavin