Jan 08 2020 05:52 AM
I am looking to validate orders working from an external source sheet and an internal one, with both having only one column in them that match up.
Is there an easy way using vlookup to match the duplicates, but also pull other information in the rows (this info needs to be entered into the external sheet with other bits of info that cannot be pulled across the other way easily to the internal sheet so struggling to get my head around some sort of formula or multiple formulas to make this possible)
In the images I have highlighted in yellow the columns that are the same info and underlines in red the other columns I need to bring across to the external sheet from the internal sheet
Any help greatly appreciated. Sorry if makes no sense!
Jan 08 2020 08:36 PM
Is it possible to upload the actual spreadsheets (after taking out any confidential information, or modifying it so as to be anonymous).... images don't really convey the full reality.
And let us know if there are going to be not just duplicates in each (external and internal) sheet, but are there going to be multiple instances of those duplicates in some or many cases? This could be a simple situation, or it could be more complex, depending on things like that.
So a more compete description would be helpful, but at the very least a good representation of the actual spreadsheets involved.
Jan 09 2020 02:19 AM - edited Jan 09 2020 02:27 AM
@mathetes I have attached a small example sheet with random info to see if that helps?
Its just understanding the step by step as every time I try something I just get errors, so I must be doing something wrong or missing something out. Many thanks for your response.
Jan 09 2020 08:52 AM
Unless I'm missing something, the sample you sent has no Order Ref No's that match. So it's kind of difficult to create a test case. Now, I could obviously modify one or two so there is a match, but I have other questions anyway. So could I ask you to answer them and give a revised sample where the fundamental conditions are met in one or two cases, i.e., where there are matches between one or two Order Refs.
Once you find a match, you said you want the data in the green on the "Internal" sheet brought over to the "External"--that's easy enough to do when there's a match [see above], and we'd have to create columns for that, easy enough...
FINALLY, you make reference to a "Final Validation Sheet"--where is that? Is it just the to-be-updated External sheet, including your comments in the red columns? Or some other sheet altogether.
Jan 10 2020 02:08 AM
@mathetes ok I will try explain a bit better. I have added a new sheet with better representation.
Ill explain each highlighted part.
The final sheet needs to be the EXTERNAL sheet.
Yellow - Find the duplicates from INTERNAL & EXTERNAL in these columns.
Green - These values also need pulling across with the web order number into EXTERNAL.
Red - Once the information from INTERNAL has been moved to EXTERNAL these are the columns I will then be able to fill in and use this sheet as the final validation sheet.
The reason why I need to do it this way around is the columns in the EXTERNAL sheet are important to be included in the final validation.
I hope I have explained it a little better. Many thanks!
Jan 10 2020 07:21 AM
Here you go. I used VLOOKUP. It looks as if EVERY code in the External sheet IS matched by one in the INTERNAL, so there's data to be brought over in every case. Is that in fact what you expected?
Jan 10 2020 08:21 AM
@mathetes This is exactly what I need to do so thanks. Could you tell me the step by step process of how you did this please if you dont mind? Many thanks
Jan 10 2020 09:19 AM
The heart of this is the very useful function VLOOKUP. (I've read that this is and has been for a long time, something like the third most widely used of the functions within Excel.) There now are some other ways to accomplish the same thing, by the way. The basic ideas in VLOOKUP consist of the following:
The VLOOKUP function gets written incorporating all those:
=VLOOKUP(#2,#1,#3,0)
So far as getting the green columns into your "External" sheet, that was simply a matter of inserting new columns and copying the headings. There needed to be a place to put the above formulas, and that's into those cells.
I hope this will help you understand how that worked. Feel free to come back with questions.
I do recommend that you look at the Excel help for more examples on VLOOKUP. Finally, not to overload you, there is another way to accomplish the same results, using a combination of INDEX and MATCH. I generally tend toward VLOOKUP, but have recently found the INDEX and MATCH functions offer some greater flexibility at times. You don't need them here, but they're worth researching.
Jan 10 2020 10:29 AM
Jan 10 2020 10:45 AM
Jan 13 2020 02:13 AM
@mathetes the reason I came here was because the info given confused me, but I have had a play with it and think I have found the best way to do this now.
Thanks so much for your help on this.