Find and copy

[Rodjk #613]

Hello,
Ok, I have a spreadsheet with two worksheets.
The second sheet will be employee names and ID numbers.
The first sheet will be set up to use a badge ID scanner.

The scanner will read the bar code on the badge, then input the badge
number to column A.
What I would like is a Macro of some sort that will fill in column B
and C with the ID Number and the Name of the employee. (The badge
number and the ID number are different.)

Is this possible?

Thanks
Rod

 

[JLatham]

I don't see any way to correlate the badge number scanned in with either of
the two items of information on the second sheet. To do this automatically
you need to have the badge number over in the second sheet also. Idealy the
setup would be (for the second sheet) 3 columns, with first column (A?)
holding the badge ID assigned, then next column could have employee name and
third column with their ID number. 2nd and 3rd columns could be other way
around, key is to have the badge number in the leftmost column of that
'table'.

Then back on the first sheet where the badge scanner is going to put the
scanned number into column A, you set up a VLOOKUP() formula in columns B and
C.

For this example we will say that you have 100 employees and their badge #s,
names and ID #s are in A1:C100 on the second sheet, with badge number in A,
name in B and ID # in C.

On first sheet in, assuming labels in row 1, data starting in row 2, put
this into B2
=IF(A2>0,VLOOKUP(A2,'Sheet2'!$A$1:$C$100,2,FALSE),"")
and in C2 put this
=IF(A2>0,VLOOKUP(A2,'Sheet2'!$A$1:$C$100,3,FALSE),"")

then fill the formulas on down the sheet. When an entry is made into column
A on that first sheet, you will either see the name and ID # for the matching
employee show up in B and C or you will see #N/A in those cells if the
scanned in badge number does not match an entry in the table on the second
sheet. We could hide the "#N/A" error - but in this case it serves as an
alert that either the scanner did not read the badge properly or that the
badge used is not assigned to someone in your employee list.

 

[Rodjk #613]

I don't see any way to correlate the badge number scanned in with either of
the two items of information on the second sheet. To do this automatically
you need to have the badge number over in the second sheet also. Idealy the
setup would be (for the second sheet) 3 columns, with first column (A?)
holding the badge ID assigned, then next column could have employee name and
third column with their ID number. 2nd and 3rd columns could be other way
around, key is to have the badge number in the leftmost column of that
'table'.

Then back on the first sheet where the badge scanner is going to put the
scanned number into column A, you set up a VLOOKUP() formula in columns B and
C.

For this example we will say that you have 100 employees and their badge #s,
names and ID #s are in A1:C100 on the second sheet, with badge number in A,
name in B and ID # in C.

On first sheet in, assuming labels in row 1, data starting in row 2, put
this into B2
=IF(A2>0,VLOOKUP(A2,'Sheet2'!$A$1:$C$100,2,FALSE),"")
and in C2 put this
=IF(A2>0,VLOOKUP(A2,'Sheet2'!$A$1:$C$100,3,FALSE),"")

then fill the formulas on down the sheet. When an entry is made into column
A on that first sheet, you will either see the name and ID # for the matching
employee show up in B and C or you will see #N/A in those cells if the
scanned in badge number does not match an entry in the table on the second
sheet. We could hide the "#N/A" error - but in this case it serves as an
alert that either the scanner did not read the badge properly or that the
badge used is not assigned to someone in your employee list.

Thank you very much!
That worked just fine, and you are right about the badge ID.
I do have it in a column on spreadsheet 2 so it can match up.
My bad, but you got it anyway!

Ok, as usual when I try these things there is an unforeseen issue.
Lets say the badge ID is '12345678'
When the barcode is scanned, it has an Initiator key built in, which
means that while the badge ID is
12345678 it is actually read as 12345678*. (* implies a wild card,
could be any letter or symbol)

I am trying to find a simple way to remove that last digit, while
keeping the cells the same.
My goal is to have the number scanned in as '12345678*' and when I
push 'Enter' to move to the next cell down, have that last number
filtered out. Then the other cells auto fill with your code above.

Is there any way to accomplish this?
I got it to work, but all cells with the code now show #VALUE and I
cannot figure out how to get rid of that w/o screwing up the
formatting. This spreadsheet needs to be as 'idiot proof' as possible!

Thanks again,
Rodjk #613

 

[Rodjk #613]

Thank you very much!
That worked just fine, and you are right about the badge ID.
I do have it in a column on spreadsheet 2 so it can match up.
My bad, but you got it anyway!

Ok, as usual when I try these things there is an unforeseen issue.
Lets say the badge ID is '12345678'
When the barcode is scanned, it has an Initiator key built in, which
means that while the badge ID is
12345678 it is actually read as 12345678*. (* implies a wild card,
could be any letter or symbol)

I am trying to find a simple way to remove that last digit, while
keeping the cells the same.
My goal is to have the number scanned in as '12345678*' and when I
push 'Enter' to move to the next cell down, have that last number
filtered out. Then the other cells auto fill with your code above.

Is there any way to accomplish this?
I got it to work, but all cells with the code now show #VALUE and I
cannot figure out how to get rid of that w/o screwing up the
formatting. This spreadsheet needs to be as 'idiot proof' as possible!

Thanks again,Rodjk#613

Ok, I got something that works.
I found this in another post:
I did a conditional formating and set A1 to "=iserror(a1)"
Then I set the format to paint the cells white.
So I lost the #VALUE error in all the unoccupied cells.

I hope this helps someone else.

Rodjk #613

 

[JLatham]

For this to work, the badge numbers on the second sheet have to be formatted
as text. Either format the entire column as TEXT or precede the badge
numbers there with a single apostrophe. This actually may be to your
advantage anyway, since this would preserve leading zeros in badge numbers
with them - and I'm betting the scanner will display a badge numbered
00040421A as 00040421A instead of 40421A.

You should also format column A on the first sheet, column where your bar
code reader places its entries, as TEXT also. Just click on the A column
identifier, then Format | Cells and choose Text from the list.

Since that last character is a 'wildcard' we only want '00040421 in our
lookup table.

Using my previous example, change the formulas to read like this:
in B2
=IF(LEN(A2)>0,VLOOKUP(LEFT(A2,LEN(A2)-1),'Sheet2'!$A:$C,2,FALSE),"")
and in C2
=IF(LEN(A2)>0,VLOOKUP(LEFT(A2,LEN(A2)-1),'Sheet2'!$A:$C,3,FALSE),"")

Since we're working with Text, it is more appropriate to use the LEN(A2)>0
test instead of just A2>0.

You should be able to get rid of your error handling with that in place also.

If there is nothing in column A it will display nothing (empty string: "")
If there is something in column A, it will take the all of the characters in
it except for the far right 1 character and compare that to entries in column
A on the second sheet and when it finds a match, returns name and employee ID.

Now, if there's an entry in A, but it can't be found in the table on Sheet2,
THEN you will see #N/A - but as I said before, I think that's a good thing
because it tells you that the scanned number does not match any
valid/authorized number in your lookup table.

If you wanted something 'really neat' and perhaps more ijit-proof, you could
go with this formula:
=IF(LEN(A2)>0,IF(ISNA(VLOOKUP(LEFT(A2,LEN(A2)-1),Sheet2!$A:$C,2,FALSE)),"No
Match",VLOOKUP(LEFT(A2,LEN(A2)-1),Sheet2!$A:$C,2,FALSE)),"")

=IF(LEN(A2)>0,IF(ISNA(VLOOKUP(LEFT(A2,LEN(A2)-1),Sheet2!$A:$C,3,FALSE)),"No
Match",VLOOKUP(LEFT(A2,LEN(A2)-1),Sheet2!$A:$C,3,FALSE)),"")

This will show the words "No Match" when an #N/A was about to come up. A
little prettier, and less confusing than just the error code.

Also, you may notice that I dropped the numbers from the range on Sheet2 -
using just the column letters. This works better for you also, probably.
You can keep adding new entries to Sheet2 without having to go back and
adjust your formulas on the first sheet.

 

[Rodjk #613]

For this to work, the badge numbers on the second sheet have to be formatted
as text. Either format the entire column as TEXT or precede the badge
numbers there with a single apostrophe. This actually may be to your
advantage anyway, since this would preserve leading zeros in badge numbers
with them - and I'm betting the scanner will display a badge numbered
00040421A as 00040421A instead of 40421A.

You should also format column A on the first sheet, column where your bar
code reader places its entries, as TEXT also. Just click on the A column
identifier, then Format | Cells and choose Text from the list.

Since that last character is a 'wildcard' we only want '00040421 in our
lookup table.

Using my previous example, change the formulas to read like this:
in B2
=IF(LEN(A2)>0,VLOOKUP(LEFT(A2,LEN(A2)-1),'Sheet2'!$A:$C,2,FALSE),"")
and in C2
=IF(LEN(A2)>0,VLOOKUP(LEFT(A2,LEN(A2)-1),'Sheet2'!$A:$C,3,FALSE),"")

Since we're working with Text, it is more appropriate to use the LEN(A2)>0
test instead of just A2>0.

You should be able to get rid of your error handling with that in place also.

If there is nothing in column A it will display nothing (empty string: "")
If there is something in column A, it will take the all of the characters in
it except for the far right 1 character and compare that to entries in column
A on the second sheet and when it finds a match, returns name and employee ID.

Now, if there's an entry in A, but it can't be found in the table on Sheet2,
THEN you will see #N/A - but as I said before, I think that's a good thing
because it tells you that the scanned number does not match any
valid/authorized number in your lookup table.

If you wanted something 'really neat' and perhaps more ijit-proof, you could
go with this formula:
=IF(LEN(A2)>0,IF(ISNA(VLOOKUP(LEFT(A2,LEN(A2)-1),Sheet2!$A:$C,2,FALSE)),"No
Match",VLOOKUP(LEFT(A2,LEN(A2)-1),Sheet2!$A:$C,2,FALSE)),"")

=IF(LEN(A2)>0,IF(ISNA(VLOOKUP(LEFT(A2,LEN(A2)-1),Sheet2!$A:$C,3,FALSE)),"No
Match",VLOOKUP(LEFT(A2,LEN(A2)-1),Sheet2!$A:$C,3,FALSE)),"")

This will show the words "No Match" when an #N/A was about to come up. A
little prettier, and less confusing than just the error code.

Also, you may notice that I dropped the numbers from the range on Sheet2 -
using just the column letters. This works better for you also, probably.
You can keep adding new entries to Sheet2 without having to go back and
adjust your formulas on the first sheet.

Hello,
The spreadsheet is fantastic, and I got rave reviews on it!
I just wanted to thank you again for the code, I could never have
figured it out on my own.

Thanks again,
Rodjk #613

 

[JLatham]

You're welcome. Glad we "wow'd" them!

Hello,
The spreadsheet is fantastic, and I got rave reviews on it!
I just wanted to thank you again for the code, I could never have
figured it out on my own.

Thanks again,
Rodjk #613