Excel problem - validation

doodah · 4 Oct 2010 at 16:39

I presume this is the correct forum. Excel question; we have a spreadsheet that has around 3,000 entries and recently entered some data manually.

Left is 'sample ID' and right is 'item ID'

The item ID is added (from another sheet) to the matching sample ID in this sheet, unfortunately most of the sample ID is not in ascending order. I tried to sort them but between the two columns lies a heap more data and Excel stated 'the operation requires the merged cells to be identically sized'.

This picture shows you after the data has been entered. In the sheet next door, there are the original two columns for sample ID and item ID. The sample ID starts from 1402001 and goes all the way to 14022933 in numerical order. Is there a way of using the two original columns and validating that the matching sample IDs and item IDs were correctly entered? Or just starting again and making Excel match the correct item ID to each of the sample IDs?

Apologies if it it doesn't make much sense

doodah · 4 Oct 2010 at 22:10

Thanks Robbie

. I will give it a try tomorrow.

doodah · 5 Oct 2010 at 10:20

Sorry some slight confusion on my part. Where exactly do I enter the formular? Do I have to highlight the appropriate columns?

Here is the sheet where the data was entered and needs to be verified, the first 800 or so are in (groups of) numerical order at which point it is like the picture in my first post:

Here are the sheet names

Finally, Sheet2 with the matched original item ID and sample/barcode ID

This is the formular I entered - =VLOOKUP(A3,'Sheet 2'$A$2:$B$3037,0,false) but it won't work.

doodah · 5 Oct 2010 at 12:01

Robbie G said:
Change the '0' for '2' (the number of columns to look across the range).

Tried that but still doesn't work. Do you enter the formular in any blank cell?

doodah · 5 Oct 2010 at 14:07

We did copy the data from the original sheet but matching the majority of 3,000 samples manually took some time, hence why I asked if it could be automated (to make sure there were no errors and/or do it again automatically).

Still not working, here is what I do step by step so you may be able to highlight what I am doing wrong:

In L3 I enter the formular, at which point A3 turns blue in the formular and around the cell. I drag the blue box all the way to the last barcode (there are some gaps if that is important). I go into Sheet2 and drag a box around barcode ID and item ID - this selection registers in the formular. I then do the same in the barcodes + ID sheet, again altering the figures in the formular. Finally I press enter but it comes up with #VALUE!

doodah · 5 Oct 2010 at 15:13

Robbie G said:
This is the part I don't understand. Sorry if I'm being thick but...why do you need to re-match them manually when surely they're already lined up on the original data sheet? Only to ensure everything is still lined up correctly?

Ok so we have the barcodes/sample IDs and item IDs. Sheet 2 has a list of sample IDs in ascending order and the matching item IDs in the neighbouring column. The other sheet has the sample IDs in no order (apart from the first few hundred), this order cannot be changed and each barcode entry has several columns of additional data. On Friday we matched the matching item IDs from Sheet 2 and placed them manually next to the correct sample IDs in the other sheet. This process is likely to have had a couple of mistakes as it was all copied and pasted. The idea for the formular is to either do the process again eliminating any human error or for it to check sample ID/item ID in the main sheet against the correct and original sample ID/item ID in Sheet 2. Hope this makes more sense

.

Thanks for your assistance Robbie