Macro That Replaces Value With Column or Tab Name

bruntonomo

Board Regular
Joined
Jul 29, 2018
Messages
67
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Attachments

  • Example.png
    Example.png
    14.2 KB · Views: 20

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
178
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2011
Messages
823
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
@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

Well-known Member
Joined
Nov 7, 2011
Messages
823
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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
ABCDE
1Building 1Building 2Building 3Building 4Outside
2Front DoorRoom 1KitchenStorageParking lot A
3Hallway 1Room 2Serving RoomRoom 4Parking lot B
4Hallway 2Room 3MaintenanceRoom 5Parling lot C
5Lunch Room
6Bathrooms
Sheet1
 

bruntonomo

Board Regular
Joined
Jul 29, 2018
Messages
67
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
ABCDE
1Building 1Building 2Building 3Building 4Outside
2Front DoorRoom 1KitchenStorageParking lot A
3Hallway 1Room 2Serving RoomRoom 4Parking lot B
4Hallway 2Room 3MaintenanceRoom 5Parling lot C
5Lunch Room
6Bathrooms
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.
 

Attachments

  • Reference.PNG
    Reference.PNG
    9.3 KB · Views: 17
  • EndResult.PNG
    EndResult.PNG
    86.9 KB · Views: 15

bruntonomo

Board Regular
Joined
Jul 29, 2018
Messages
67
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2011
Messages
823
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

@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

Well-known Member
Joined
Nov 7, 2011
Messages
823
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Based on the limited info received thus far, the following pic is all I can offer so far:



Temp.PNG



Here it is in XL2BB form:

Cell Formulas
RangeFormula
G2:G21G2=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
AB
1Camera 1Zone 1A(West of J Drive Aisle)
2Camera 2Zone 1A(West of J Drive Aisle)
3Camera 3Zone 1A(West of J Drive Aisle)
4Camera 4Zone 1A(West of J Drive Aisle)
5Camera 5Zone 1A(West of J Drive Aisle)
6Camera 6Zone 1B(East of J Drive Aisle)
7Camera 7Zone 1B(East of J Drive Aisle)
8Camera 8Zone 1B(East of J Drive Aisle)
9Camera 9Zone 1B(East of J Drive Aisle)
10Camera 10Zone 1C(Annex/Expansion)
11Camera 11Zone 1C(Annex/Expansion)
12Camera 12Zone 1C(Annex/Expansion)
13Camera 13Zone 2A(East of R/S Belt)
14Camera 14Zone 2A(East of R/S Belt)
15Camera 15Zone 2A(East of R/S Belt)
16Camera 16Zone 2A(East of R/S Belt)
17Camera 17Zone 2A(East of R/S Belt)
18Camera 18Zone 2A(East of R/S Belt)
19UnknownZone Unknown
Camera Conversion
 
Last edited:

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
178
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 29, 2018
Messages
67
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
@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.
 

Forum statistics

Threads
1,136,765
Messages
5,677,618
Members
419,707
Latest member
Anna vib

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top