excelnoob_67
New Member
- Joined
- Jul 8, 2022
- Messages
- 21
- Office Version
- 365
- Platform
- Windows
So I have a doozy of a question.
I have 2 tables.
Table 1: contains a list "Sites=siteID" on each row. Columns contain "Device Name" 1-30
Table 2: contains "SiteID", "Device Name" SiteID is over multiple rows
See examples
Table 1:
Table 2:
Now what I would like to do is match / vlookup the data in table 2 and update table 1 column Device Name.
In my head it would be something like:
lookup 7412 if device name = 7412001 add it column 001
lookup 7412 if device name = 7412002 add it column 002
And so on and so on.
first issue is vlookup wont look past the first siteID on table 2. Table is not a large export and can be formatting another way to make is easier to look up.
I guess the real question is how can I tell excel if the siteid matches and the device id ends in 001/2/3 add to the relevant columns
This isn't a simple transpose will fix situation
Any help would be appreciate
Thanks
I have 2 tables.
Table 1: contains a list "Sites=siteID" on each row. Columns contain "Device Name" 1-30
Table 2: contains "SiteID", "Device Name" SiteID is over multiple rows
See examples
Table 1:
| Site ID | Device Name 001 | Device Name 002 | Device Name 003 |
| 7412 | LAP7412001 | LAP7412002 | LAP7412003 |
| 9632 | LAP9632001 | LAP9632002 | LAP9632003 |
Table 2:
| 7412 | LAP7412001 |
| 7412 | LAP7412002 |
| 7412 | LAP7412003 |
| 9632 | LAP9632001 |
| 9632 | LAP9632002 |
| 9632 | LAP9632003 |
Now what I would like to do is match / vlookup the data in table 2 and update table 1 column Device Name.
In my head it would be something like:
lookup 7412 if device name = 7412001 add it column 001
lookup 7412 if device name = 7412002 add it column 002
And so on and so on.
first issue is vlookup wont look past the first siteID on table 2. Table is not a large export and can be formatting another way to make is easier to look up.
I guess the real question is how can I tell excel if the siteid matches and the device id ends in 001/2/3 add to the relevant columns
This isn't a simple transpose will fix situation
Any help would be appreciate
Thanks
