LinkBack URL
About LinkBacks
Hi
This is a bit complicated so I have attached a workbook with explanations in the columns.
Thanks for any help.
Hi
This is a bit complicated so I have attached a workbook with explanations in the columns.
Thanks for any help.
Wouldn't
=AD2
in AH2
and copied down give you your expected results?
No because the the Ref1 figures are from one report and the Ref2 are from another. That's why I said there were less rows of data in one column than the other.
The point of the excercise is to reconcile the two columns of figures and I just need to get the figures in column AH to be in the same order as those in Column AD so that figures are the same and I can where there are variances.
It was difficult to show on the example but Column AH has blank cells where there was no corresponding number in the vlookup.
Can you post your expected results?
Hi yudlugar,
Not easily the file is so big.
Basically the Ref1 Numbers in Col AD and the Ref1 Units in Col AJ are from the same file.
The numbers in Ref2 are fom a different file.
My objective is to look at the figure in say AD2 - 9780486266848 and if that figure anywhere in column AF then return 248 in Col AK2. If 9780486266848 doesn't exist then return a zero in col AK2.
Hope thant makes sense.
Last edited by timbo1957; 11-15-2013 at 04:57 AM.
ok
=IF(COUNTIF(AF1:AF1000,"="&AD2)>0,248,0)
hi timbo. what you want is really bizarre. perhaps the file is a little confusing. my assumption is your worksheet in 1 file has only got:
- Column AD (Ref1)
- Column AJ (Ref1 Units)
is that right? you want to compare if the Ref2 has the same number of units? when you are going to use VLOOKUP to pick up the Ref2 units in AK2, you are already looking for AD2. why do you need to have a Column AH doing a VLOOKUP? so if you really need one, it's like what yudlugar suggested in post #2. if your concern is about Ref2 having lesser records & you don't want it to show up in column AH, then you should do the VLOOKUP the other way round in AH2:
=IF(ISNA(VLOOKUP(AD2,$AF$1:$AF$14452,1,0)),0,VLOOKUP(AD2,$AF$1:$AF$14452,1,0))
i'm assuming you might still have to use Excel 2003? otherwise, Excel 2007 & above can use a shorter version:
=IFERROR(VLOOKUP(AD2,$AF$1:$AF$14452,1,FALSE),0)
Last edited by benishiryo; 11-15-2013 at 05:20 AM. Reason: edited content
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Slight change to my formula:
=IF(COUNTIF($AF$1:$AF$1000,"="&AD2)>0,248,0)
You need to replace 248 with the location of the reference...
Genius yudlugar!
I wound never have thought of using Countif.
Many thanks.
I'd add to your rep. but it won't let me!
Hi yudlugar,
One more question. I need to put your calcuation into a piece of code and I am obvisously doing something wrong becuase it is returning FALSE any help would be appreciated.
Please Login or Register to view this content.
please don't assume because someone does formulas/functions they also do code which is a different if complimentary field. if you have a vba question mark this as solved since the countif or whatever does what you want then start a new thread in the vba sub forum linking to this one if you feel the need to. thanx
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
martin, in this case, I'd helped timbo on a couple of other vba threads recently...
as a vba formula it would be:
Please Login or Register to view this content.
Sorry Martin,
I didn't think - Sincere Apologies.
Thanks yudlugar, that works perfectly. Sorry about the not putting the post on the right forum.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks