zookeepertx
Well-known Member
- Joined
- May 27, 2011
- Messages
- 576
- Office Version
- 365
- Platform
- Windows
I have 2 workbooks: 1 contains data in columns A-K & the other is a log containing data in columns A-P. On the data sheet, col C is a vendor name & col A is a shortened code for the vendor. On the log sheet, col E is the first 9 characters of the vendor name followed by a / & the vendor number.
I need to match up the 9 characters of the vendor name on the log with the first 9 characters of the vendor name on the data sheet & return the vendor's code from col A of the data sheet in col R of the log.
EXCEPT: if the vendor name has a duplicate just above or below it on the data sheet or the vendor code has a duplicate just above or below it on the data sheet, then I need the word VERIFY in col R of the log. It would be best if the cells that return VERIFY could be red & any cells in col R without a match on the data sheet could be yellow with something like NO MATCH in them.
Here is an example of the data sheet (left) & the log sheet (right) as they start out:
[TABLE="width: 461"]
<TBODY>[TR]
[TD="width: 60, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]Col A
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Col C
[/TD]
[TD="class: xl67, width: 43, bgcolor: white"][/TD]
[TD="class: xl67, width: 41, bgcolor: white"][/TD]
[TD="width: 49, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Col E
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 1
[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]WIDS
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Widgets and Stuff
[/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"]Row 1
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Justin Mo/27584
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 2
[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]BUCO
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Bungee Cords Intl
[/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"]Row 2
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Wires N S/13579
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 3
[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]WNST
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Wires N Strings
[/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"]Row 3
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Dictionar/14893
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 4
[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]DICT
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Dictionaries R Us
[/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"]Row 4
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Dictionar/14893
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 5
[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]DICR
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Dictionaries R Us
[/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"]Row 5
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Northern /98347
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 6
[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]JUST
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Just A Dollar
[/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"]Row 6
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Widgets a/12345
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 7
[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]JUST
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Justin Morgan
[/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"]Row 7
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Bungee Co/24680
[/TD]
[/TR]
</TBODY>[/TABLE]
As you can see, Dictionaries R Us has a duplicate in the name column & JUST has a duplicate in the code column. Also, on the log sheet, Row 5 has no match on the data sheet. So, the desired result on the log sheet would be:
[TABLE="width: 234"]
<TBODY>[TR]
[TD="width: 49, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Col E
[/TD]
[TD="class: xl66, width: 107, bgcolor: transparent"]Col R
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 1
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Justin Mo/27584
[/TD]
[TD="class: xl67, width: 107, bgcolor: red"]VERIFY
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 2
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Wires N S/13579
[/TD]
[TD="class: xl66, width: 107, bgcolor: transparent"]WNST
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 3
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Dictionar/14893
[/TD]
[TD="class: xl67, width: 107, bgcolor: red"]VERIFY
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 4
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Dictionar/14893
[/TD]
[TD="class: xl67, width: 107, bgcolor: red"]VERIFY
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 5
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Northern /98347
[/TD]
[TD="class: xl68, width: 107, bgcolor: yellow"]NO MATCH
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 6
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Widgets a/12345
[/TD]
[TD="class: xl66, width: 107, bgcolor: transparent"]WIDS
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 7
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Bungee Co/24680
[/TD]
[TD="class: xl66, width: 107, bgcolor: transparent"]BUCO
[/TD]
[/TR]
</TBODY>[/TABLE]
I would REALLY appreciate any help on this; I've been studying on it & can't seem to figure out how to do it. Thanks!
(Just got Excel 2010 & still trying to figure it out)
I need to match up the 9 characters of the vendor name on the log with the first 9 characters of the vendor name on the data sheet & return the vendor's code from col A of the data sheet in col R of the log.
EXCEPT: if the vendor name has a duplicate just above or below it on the data sheet or the vendor code has a duplicate just above or below it on the data sheet, then I need the word VERIFY in col R of the log. It would be best if the cells that return VERIFY could be red & any cells in col R without a match on the data sheet could be yellow with something like NO MATCH in them.
Here is an example of the data sheet (left) & the log sheet (right) as they start out:
[TABLE="width: 461"]
<TBODY>[TR]
[TD="width: 60, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]Col A
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Col C
[/TD]
[TD="class: xl67, width: 43, bgcolor: white"][/TD]
[TD="class: xl67, width: 41, bgcolor: white"][/TD]
[TD="width: 49, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Col E
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 1
[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]WIDS
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Widgets and Stuff
[/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"]Row 1
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Justin Mo/27584
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 2
[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]BUCO
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Bungee Cords Intl
[/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"]Row 2
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Wires N S/13579
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 3
[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]WNST
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Wires N Strings
[/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"]Row 3
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Dictionar/14893
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 4
[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]DICT
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Dictionaries R Us
[/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"]Row 4
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Dictionar/14893
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 5
[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]DICR
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Dictionaries R Us
[/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"]Row 5
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Northern /98347
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 6
[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]JUST
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Just A Dollar
[/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"]Row 6
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Widgets a/12345
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 7
[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]JUST
[/TD]
[TD="class: xl66, width: 167, bgcolor: transparent"]Justin Morgan
[/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl67, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"]Row 7
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Bungee Co/24680
[/TD]
[/TR]
</TBODY>[/TABLE]
As you can see, Dictionaries R Us has a duplicate in the name column & JUST has a duplicate in the code column. Also, on the log sheet, Row 5 has no match on the data sheet. So, the desired result on the log sheet would be:
[TABLE="width: 234"]
<TBODY>[TR]
[TD="width: 49, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Col E
[/TD]
[TD="class: xl66, width: 107, bgcolor: transparent"]Col R
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 1
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Justin Mo/27584
[/TD]
[TD="class: xl67, width: 107, bgcolor: red"]VERIFY
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 2
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Wires N S/13579
[/TD]
[TD="class: xl66, width: 107, bgcolor: transparent"]WNST
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 3
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Dictionar/14893
[/TD]
[TD="class: xl67, width: 107, bgcolor: red"]VERIFY
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 4
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Dictionar/14893
[/TD]
[TD="class: xl67, width: 107, bgcolor: red"]VERIFY
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 5
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Northern /98347
[/TD]
[TD="class: xl68, width: 107, bgcolor: yellow"]NO MATCH
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 6
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Widgets a/12345
[/TD]
[TD="class: xl66, width: 107, bgcolor: transparent"]WIDS
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Row 7
[/TD]
[TD="class: xl66, width: 156, bgcolor: transparent"]Bungee Co/24680
[/TD]
[TD="class: xl66, width: 107, bgcolor: transparent"]BUCO
[/TD]
[/TR]
</TBODY>[/TABLE]
I would REALLY appreciate any help on this; I've been studying on it & can't seem to figure out how to do it. Thanks!
(Just got Excel 2010 & still trying to figure it out)