This one is beating me! 
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
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.
<o></o>
GOAL: <o></o>
1. To append all ServerNames from different sources into 1 column and remove duplicates.<o></o>
2. To determine what server are being backed up and what ones are not.<o></o>
Data Files:<o></o>
1. ServerNames from 4 different sources from different technical teams. <o></o>
2. ServerNames from 4 different sources from backup software.
<o></o>
<o></o>
Issues 1:<o></o>
I want to take all ServerNames from all 8 sources and put them in one column and remove duplicates on a new worksheet.<o></o>
Trouble finding a way to read each worksheet and append them to a new worksheet with duplicates removed.<o></o>
So, read sheet 1, create new column in a new worksheet until cell is blank, read next sheet and append until blank cell is found, etc… <o></o>
This new worksheet becomes the master server list (Dashboard).<o></o>
Sample code I am using for this:<o></o>
None – just cutting and pasting into new column and removing duplicates by hand.
<o></o>
Issues 2:<o></o>
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”.
<o></o>
<o></o>
Sample code I am using for this:<o></o>
=(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")))))<o></o>
Results: Just the first VLOOKUP works and returns the correct value. The rest return “Not Backed Up”.<o></o>
BECH, BENY, AVADRT, AVAHQ are all Group Names assign to the backup data sources.
<o></o>
Output sample hoping for:<o></o>
ServerName BE-CH<o></o>
ServerName BE-NY<o></o>
ServerName AVA-DRT<o></o>
ServerName Not Backed UP<o></o>
ServerName BE-CH<o></o>
Etc……
<o></o>
<o></o>
Thank you in advance for any help you can supply!
<o></o>
<o></o>
Bob<o></o>
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o> 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.
<o
></o> GOAL: <o
></o>1. To append all ServerNames from different sources into 1 column and remove duplicates.<o
></o>2. To determine what server are being backed up and what ones are not.<o
></o>Data Files:<o
></o>1. ServerNames from 4 different sources from different technical teams. <o
></o>2. ServerNames from 4 different sources from backup software.
<o
></o> <o
></o>Issues 1:<o
></o>I want to take all ServerNames from all 8 sources and put them in one column and remove duplicates on a new worksheet.<o
></o>Trouble finding a way to read each worksheet and append them to a new worksheet with duplicates removed.<o
></o>So, read sheet 1, create new column in a new worksheet until cell is blank, read next sheet and append until blank cell is found, etc… <o
></o>This new worksheet becomes the master server list (Dashboard).<o
></o>Sample code I am using for this:<o
></o>None – just cutting and pasting into new column and removing duplicates by hand.
<o
></o> Issues 2:<o
></o>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”.
<o
></o> <o
></o>Sample code I am using for this:<o
></o>=(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")))))<o
></o>Results: Just the first VLOOKUP works and returns the correct value. The rest return “Not Backed Up”.<o
></o>BECH, BENY, AVADRT, AVAHQ are all Group Names assign to the backup data sources.
<o
></o> Output sample hoping for:<o
></o>ServerName BE-CH<o
></o>ServerName BE-NY<o
></o>ServerName AVA-DRT<o
></o>ServerName Not Backed UP<o
></o>ServerName BE-CH<o
></o>Etc……
<o
></o> <o
></o>Thank you in advance for any help you can supply!
<o
></o> <o
></o>Bob<o
></o>
Last edited:
