98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel
Excel
Resolved · High Priority · Version 2010
Wed 23 Nov 2016, 17:18Delegate Stephen said...
Stephen has attended:
Excel Intermediate course
Excel Advanced course
Excel
Hi , you will probably solve this in 30 seconds .. a common issue
Anyway I have 1000 rows of data across 2 tabs , same format , but different order , much of it the same . The column under investigation is C on both sheets , labelled 'Account_ID' .
I need to identify which account numbers match , and which don't .
the 2 TABS are labelled 3.2 Credit / 3.7 Debit
Sure it'll be a VLOOKUP or MATCH of some sort . Any help much appreciated , if you could advise a formula that will solve .
(could be the 1st of a few Q's coming up over the next few days / weeks ...... :o)
tks guys
Stephen
Thu 24 Nov 2016, 10:09Trainer Jens said...
RE: Excel
Hi Stephen,
Thank you for the forum question.
Yes you already have some good ideas.
I would use the MATCH. If you in a column in the row where you have the first Account Id you want to match in the worksheet where you want to test if you have the same Account Id in the second worksheet type:
=match(C2,$C$2:$C$1000,0)
Then copy down the formula. If the match return #N/A it cannot match the Account Id on the second worksheet. If it can match it, it will return the row number of the match.
I hope this make sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 24 Nov 2016, 10:14Trainer Jens said...
RE: Excel
Hi Stephen,
I made a tiny mistake in my answer to you.
The reference C2 in the formula is from the sheet where you have the match function. You will have to select the range on the second worksheet. It will not be $C$2:$C$1000 but 'the name of the sheet'!$C$2:$C$1000. You just need to select the range then Excel will put in the name from the sheet.
I hope this make sense too
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 24 Nov 2016, 12:03Delegate Stephen said...
RE: Excel
Hi Jens , tks for your reply .
Still not quite there ... and returning N/A's right down the list .
So for row 3 I have =MATCH(C3,'3.2 Credit'!$C$1000,0)
Row 4 =MATCH(C4,'3.2 Credit'!$C$1000,0)
Row 5 =MATCH(C5,'3.2 Credit'!$C$1000,0)
I am using F4 to complete the formula during the keystroke process ...... I think it may be the drag down the column ? Not copying correctly ? The match column should return the positive duplicates , plus the actual number
Tks in advance
Regds
Stephen
Thu 24 Nov 2016, 12:24Trainer Jens said...
RE: Excel
Hi Stephen,
Please amend the formula in row 3 and copy it down. The range must be the whole range on the '3.2 Credit' sheet.
=MATCH(C3,'3.2 Credit'!$C$3:$C$1000,0)
I hope this will do the job, but please let me know if not.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 24 Nov 2016, 12:15Delegate Stephen said...
RE: Excel
Have also tried this
=VLOOKUP(C3, '3.2 Credit'!$C$3:$C$1002,0)
=VLOOKUP(C4, '3.2 Credit'!$C$3:$C$1002,0)
=VLOOKUP(C5, '3.2 Credit'!$C$3:$C$1002,0)
Note the first 3 are exact matches , but it returns
#VALUE!
#VALUE!
#VALUE!
?
Tks again
Thu 24 Nov 2016, 12:27Trainer Jens said...
RE: Excel
Hi Stephen,
If you want to use the Vlookup. Please amend your Vlookup to:
=VLOOKUP(C3, '3.2 Credit'!$C$3:$C$1002,1,0)
Good luck and please let me know if it works.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 24 Nov 2016, 14:50Delegate Stephen said...
RE: Excel
Perfect , thanks Jens , the magic F4 button doing the trick again ..... can you just remind me what the 1,0 mean at the end of the formula again ?
0 = True match ? , it was the 1 before I was missing
We got there !
tks
Stephen
Thu 24 Nov 2016, 15:11Trainer Jens said...
RE: Excel
Hi Stephen,
I am happy to hear it is working.
The Vlookup needs 3 or 4 informations. Lookup_Value is the value you want to lookup. Table_array is the range in which you want to find the lookup_value, Col_index_num is the column number inside the Table_array from which you want to return the value from and [Range_lookup] (the square brackets indicate that it is optional to enter the information) tells the function if you are looking for a perfect match. If you type 0 or false you are only looking for a perfect match. If you leave it empty, type 1 or true the function will look for a perfect match or nearest lowest value.
In your situation you want to look for a perfect match. And 1 in column_index_num because you want the lookup function to return the value from the single column you have in the table_array
=Vlookup(Lookup_Value,Table_Array,Col_index_Num,[range_lookup])
=VLOOKUP(C3, '3.2 Credit'!$C$3:$C$1002,1,0)
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 1 Dec 2016: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Using basic functions without doing formulasWhen you highlight figures Autocalc tells you the total in the bottom right of the screen, but if you right click on the sum it will give you some basic functions. The functions are Min, Max, Average, Sum, Count, and Count Nums. |
