Macro That Replaces Value With Column or Tab Name

[bruntonomo]

I have a list of roughly 150 cameras that are spread out throughout several buildings. When I get a report with a selected camera name, I would like to associate that with the appropriate zone. I would think the best way to do this would be to develop a macro that can be run to change all the camera name values in a given column to the name of a column header or tab name with which they are grouped. Please see the attached image.

In this case, when the macro would be run, any entries in a column that say Front Door, Hallway 1, Hallway 2, Lunch Room, or Bathrooms would be converted to Building 1. Room 1, Room 2, and Room 3 would be changed to Building 2.

I could go through each day and manually change these entries to what I want them to be, but it would take a considerable amount of time.

 

[rondeondo]

Hi Bruntonomo
you can probably do a lookup, make a list of the camera name you get with a column next to is giving the label you want to apply.
So your lookup list will look something like:
Camera Label | Desired Label
Room 1 | Building 2
Kitchen | Building 3
etc for all cameras

This list can be on another worksheet

Then in your main sheet use =vlookup(<camera label>, <lookup sheet a:b>,2,false)
where <camera label> is the cell with the camera name
<lookup sheet> is the sheet you put the lookup table in

The advantage to this is you just copy the vlookup formula through the column and if any cameras are added just add them to the list.

 

[johnnyL]

@bruntonomo Could you post what your desired result would look like?

It sounds like you want delete the specific camera name and just report the zone names?

Example: if 'Front Door' is initially received, you want to change that entry to 'Building 1'? Basically your final results will just report Buildings or Outside?

 

[johnnyL]

And is your data of received specific camera names all in one column that you want converted to zone names? Multiple columns of received specific camera names to be converted to zone names? Please be more specific in your request. Post a result of what the data that you are dealing with looks like.

Here is what you have submitted thus far:

Book2
	A	B	C	D	E
1	Building 1	Building 2	Building 3	Building 4	Outside
2	Front Door	Room 1	Kitchen	Storage	Parking lot A
3	Hallway 1	Room 2	Serving Room	Room 4	Parking lot B
4	Hallway 2	Room 3	Maintenance	Room 5	Parling lot C
5	Lunch Room
6	Bathrooms
Sheet1

 

[bruntonomo]

And is your data of received specific camera names all in one column that you want converted to zone names? Multiple columns of received specific camera names to be converted to zone names? Please be more specific in your request. Post a result of what the data that you are dealing with looks like.

Here is what you have submitted thus far:

Book2
	A	B	C	D	E
1	Building 1	Building 2	Building 3	Building 4	Outside
2	Front Door	Room 1	Kitchen	Storage	Parking lot A
3	Hallway 1	Room 2	Serving Room	Room 4	Parking lot B
4	Hallway 2	Room 3	Maintenance	Room 5	Parling lot C
5	Lunch Room
6	Bathrooms
Sheet1

I'm terribly sorry for the late response. I've been monitoring my email, but messages went to junk mail and I didn't see it until now.

You have the correct idea about what I'm looking for. The reporting software we use spits out camera names into an Excel document. I want to group lists of cameras to specific areas so that we can show which areas of the building we're paying attention to (how much attention is paid to each area). Instead of manually going through the column and changing each entry to the correct area, I can just run a macro to do this for me.

Please understand that I can't give you exact camera names. I can give you zone names. The reference image shows how I want cameras grouped. Cameras 1-5 will belong to Zone 1A. Cameras 6 - 9 belong to Zone 1B. The End Result image shows what things should look like after the macro is run (zones are inserted into column F. For example, before I run the macro, cell F2 would say Camera 6. After running the macro, it will say Zone 1B (West of J Drive Aisle) because Camera 6 belongs to the Zone 1B group.

 

[bruntonomo]

Hi Bruntonomo
you can probably do a lookup, make a list of the camera name you get with a column next to is giving the label you want to apply.
So your lookup list will look something like:
Camera Label | Desired Label
Room 1 | Building 2
Kitchen | Building 3
etc for all cameras

This list can be on another worksheet

Then in your main sheet use =vlookup(<camera label>, <lookup sheet a:b>,2,false)
where <camera label> is the cell with the camera name
<lookup sheet> is the sheet you put the lookup table in

The advantage to this is you just copy the vlookup formula through the column and if any cameras are added just add them to the list.

Hi rondeondo,

I think a vlookup (or any kind of lookup formula) would work if I knew what values I was looking for every time I receive a report. In my case the camera name is a variable that could be anything so I would have to go through and tweak the formula each time to look it up. At that point it would be the same as manually inputting things into each cell. I certainly appreciate your willingness to reply though!

 

[johnnyL]

@bruntonomo, again you are leaving out information that is needed to help you.

Your 'EndResult' picture includes zones that you haven't previously mentioned, for example Zones 3A, 3B, 5B, & 5C.

How are we to know which cameras are covering those zones and probably other unmentioned zones?

We can't guess which cameras cover particular zones. You have to provide the information to us. How else would we replace the cameras in the F column with zones?

 

[johnnyL]

Based on the limited info received thus far, the following pic is all I can offer so far:

Here it is in XL2BB form:

bruntonomoV1.xlsm
	A	B	C	D	E	F	G
1
2						Camera 6	Zone 1B(East of J Drive Aisle)
3						Camera 1	Zone 1A(West of J Drive Aisle)
4						Camera 10	Zone 1C(Annex/Expansion)
5						Camera 13	Zone 2A(East of R/S Belt)
6						Camera 11	Zone 1C(Annex/Expansion)
7						Unknown	Zone Unknown
8						Unknown	Zone Unknown
9						Camera 7	Zone 1B(East of J Drive Aisle)
10						Camera 14	Zone 2A(East of R/S Belt)
11						Camera 12	Zone 1C(Annex/Expansion)
12						Camera 8	Zone 1B(East of J Drive Aisle)
13						Camera 10	Zone 1C(Annex/Expansion)
14						Unknown	Zone Unknown
15						Unknown	Zone Unknown
16						Camera 15	Zone 2A(East of R/S Belt)
17						Unknown	Zone Unknown
18						Unknown	Zone Unknown
19						Unknown	Zone Unknown
20						Camera 11	Zone 1C(Annex/Expansion)
21						Camera 12	Zone 1C(Annex/Expansion)
Report

Cell Formulas
Range	Formula
G2:G21	G2	=INDEX('Camera Conversion'!B:B,MATCH(Report!F2,'Camera Conversion'!A:A,0))

And here is the table for the conversions in XL2BB format:

bruntonomoV1.xlsm
	A	B
1	Camera 1	Zone 1A(West of J Drive Aisle)
2	Camera 2	Zone 1A(West of J Drive Aisle)
3	Camera 3	Zone 1A(West of J Drive Aisle)
4	Camera 4	Zone 1A(West of J Drive Aisle)
5	Camera 5	Zone 1A(West of J Drive Aisle)
6	Camera 6	Zone 1B(East of J Drive Aisle)
7	Camera 7	Zone 1B(East of J Drive Aisle)
8	Camera 8	Zone 1B(East of J Drive Aisle)
9	Camera 9	Zone 1B(East of J Drive Aisle)
10	Camera 10	Zone 1C(Annex/Expansion)
11	Camera 11	Zone 1C(Annex/Expansion)
12	Camera 12	Zone 1C(Annex/Expansion)
13	Camera 13	Zone 2A(East of R/S Belt)
14	Camera 14	Zone 2A(East of R/S Belt)
15	Camera 15	Zone 2A(East of R/S Belt)
16	Camera 16	Zone 2A(East of R/S Belt)
17	Camera 17	Zone 2A(East of R/S Belt)
18	Camera 18	Zone 2A(East of R/S Belt)
19	Unknown	Zone Unknown
Camera Conversion

 

[rondeondo]

Hi Bruno
is there anything consistent with the camera names? Does the list of camera names with locations come with the other data? If so we may be able to work with that, along the lines of what @johnnyL has done. I've got a macro for converting a table like in your reference image into a pair of columns that can then be used for a lookup. But to go further we do need to know what changes and what stays the same from one load to the next.

 

[bruntonomo]

@bruntonomo, again you are leaving out information that is needed to help you.

Your 'EndResult' picture includes zones that you haven't previously mentioned, for example Zones 3A, 3B, 5B, & 5C.

How are we to know which cameras are covering those zones and probably other unmentioned zones?

We can't guess which cameras cover particular zones. You have to provide the information to us. How else would we replace the cameras in the F column with zones?

@johnnyL I'm sorry, but I cannot share actual camera names. I don't mean to be rude or difficult. Camera names are considered confidential information where I work. Some camera names have descriptor information that eludes to business practices and policy dictates that I can't share that information. That's why I provided generic names. I did provide zone names because those are very general and are not used by the client in any meaningful way other than a general descriptor. That's why I've been looking for a general solution that can be modified with specific details rather than a ready to deploy solution with all the details.