How to validate multiple columns from a single duplicate column in 2 worksheets

[jhc163]

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!

  

[#2]

@jhc163 

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.

[#3]

@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.

[#4]

@jhc163 

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.

[#5]

@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!

[#6]

@jhc163 

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?

[#7]

@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

[#8]

@jhc163 

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:

1. There's a "table" containing information that you want to retrieve. (In your case, that's the information on the "Internal" sheet.)
2. There is some unique "index" or "identifier" for each row in that table. In your case, that's the "order ref no."
3. And then there are columns of information, some specific one containing the information you want to retrieve. Those columns are offset from the identifier column by a specific number.
4. There's the choice of "Exact Match" or not, represented by 0 or 1 respectively.

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.

[#9]

I will be honest this information doesnt help me to understand. If you were able to tell me a step by step on what you did with each section and the codes that would help me a lot. I find it hard to understand without having a step by step guide on the example. If you cannot do that no problem it would just help me understand the process a lot better. Many thanks

[#10]

Have you tried looking at the Excel help text on the VLOOKUP function? Or at an Excel reference text (i.e., a written book). If we were able to sit down together, side-by-side, maybe I could walk you through an example, but limited as we are to messaging back and forth, I can't imagine doing any better than what you'll find in the help text.

I will say this: the heart of a LOT of Excel functions is visualizing your data as a table, a set of rows of data arrayed in columns. Once you grasp that, the VLOOKUP becomes clear. All it's doing is looking up a given order number and then, once it finds it, going across the table the prescribed number of columns and retrieving the info and placing it in the cell where the formula resides.
But the table concept is at the heart, and until you grasp that, it will remain a mystery. Look at some simple examples, in the Help text...create some of your own.

[#11]

@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.