This one is beating me!
I have data in 8 worksheets and I am searching the data and summarizing the data for results on a new worksheet, sort of a Dashboard.
GOAL:
1. To append all ServerNames from different sources into 1 column and remove duplicates on a new worksheet.
2. To determine what servers are being backed up and what ones are not.
Data Files:
1. ServerNames from 4 different sources from different technical teams.
2. ServerNames from 4 different sources from backup software.
Issues 1:
I want to take all ServerNames from all 8 sources and put them in one column and remove duplicates on a new worksheet.
Trouble finding a way to read each worksheet and append them to a new worksheet with duplicates removed.
So, read sheet 1, write ServerName new column in a new worksheet until end of column, read next sheet and append until until end of column, etc…
This new worksheet becomes the master server list (Dashboard).
Sample code I am using for this:
None – just cutting and pasting into new column and removing duplicates by hand.
Issues 2:
I want to read each ServerName from the master server list then look in each Backup worksheet and see if the name matches. If there is a match put name of backup software in next column next to ServerName. Do this for all 4 backup data sources per ServerName. If ServerName is not in any of the three backup data sources then write “Not Backed up”.
Sample code I am using for this:
=(IF(VLOOKUP(B8,BECH,2,FALSE)<>"","BE-CH",IF(VLOOKUP(B8,BENY,2,FALSE)<>"","BE-NY",IF(VLOOKUP(B8,AVADRT,2,FALSE)<>"","AVA-DRT",IF(VLOOKUP(B8,AVAHQ,2,FALSE)<>"","AVA-HQ","Not Backed Up")))))
Results:
Just the first VLOOKUP works and returns the correct value. The rest return “Not Backed Up”.
BECH, BENY, AVADRT, AVAHQ are all Group Names assign to the backup data sources.
Output sample hoping for:
ServerName BE-CH
ServerName BE-NY
ServerName AVA-DRT
ServerName Not Backed UP
ServerName BE-CH
Etc……
Thank you in advance for any help you can supply!
Bob <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->
I have data in 8 worksheets and I am searching the data and summarizing the data for results on a new worksheet, sort of a Dashboard.
GOAL:
1. To append all ServerNames from different sources into 1 column and remove duplicates on a new worksheet.
2. To determine what servers are being backed up and what ones are not.
Data Files:
1. ServerNames from 4 different sources from different technical teams.
2. ServerNames from 4 different sources from backup software.
Issues 1:
I want to take all ServerNames from all 8 sources and put them in one column and remove duplicates on a new worksheet.
Trouble finding a way to read each worksheet and append them to a new worksheet with duplicates removed.
So, read sheet 1, write ServerName new column in a new worksheet until end of column, read next sheet and append until until end of column, etc…
This new worksheet becomes the master server list (Dashboard).
Sample code I am using for this:
None – just cutting and pasting into new column and removing duplicates by hand.
Issues 2:
I want to read each ServerName from the master server list then look in each Backup worksheet and see if the name matches. If there is a match put name of backup software in next column next to ServerName. Do this for all 4 backup data sources per ServerName. If ServerName is not in any of the three backup data sources then write “Not Backed up”.
Sample code I am using for this:
=(IF(VLOOKUP(B8,BECH,2,FALSE)<>"","BE-CH",IF(VLOOKUP(B8,BENY,2,FALSE)<>"","BE-NY",IF(VLOOKUP(B8,AVADRT,2,FALSE)<>"","AVA-DRT",IF(VLOOKUP(B8,AVAHQ,2,FALSE)<>"","AVA-HQ","Not Backed Up")))))
Results:
Just the first VLOOKUP works and returns the correct value. The rest return “Not Backed Up”.
BECH, BENY, AVADRT, AVAHQ are all Group Names assign to the backup data sources.
Output sample hoping for:
ServerName BE-CH
ServerName BE-NY
ServerName AVA-DRT
ServerName Not Backed UP
ServerName BE-CH
Etc……
Thank you in advance for any help you can supply!
Bob <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->
<!-- controls -->
