Help with merging and sorting Data

[Staticman]

Hello I need some help (well a lot actually)

I have only just started with VBA and I am way out of my depth with this one.



I have a work Book with over 100 sheets each sheet represents a different site.

Each site has a different range of IP addresses (approx 3000)

 with a name in the next column,

Only about 100 of these refer to printers. 

These sites have all been updated with new printers and now I have a lot of work to do.



I have all the new IP Addresses and Printer Names and Types in some other workbooks 

and I need to merge them with the first, also shortening the printer name and type.

For the Printer Name I need to keep the 02 03 etc as this indicates which server it's on.

For the Printer Type I also need to shorten the name to save on space and the printer 

name needs to be in bold if it has been updated.

The printers IP ranges are the same for each site ie: 192.168.1.1 to 192.168.1.100



I know this is a lot to ask but any help would be appreciated



Dave







Work Book A

column A	column B	column c	column d	column e	column f

IPAddress	Server		Comment		IPAddress	Server		Comment

192.168.1.1	reserved                        192.168.1.30	reserved

192.168.1.2	reserved			192.168.1.31	reserved

192.168.1.3	Sandman				etc...

192.168.1.4	Nel

192.168.1.5	Joker

192.168.1.6	Vader

192.168.1.7	Luke

192.168.1.8	reserved

192.168.1.9	Han

192.168.1.10	Falcon

192.168.1.11	reserved

192.168.1.12	turner

192.168.1.13	reserved

192.168.1.14	OldGirl

192.168.1.15	reserved

192.168.1.16	reserved

192.168.1.17	reserved

192.168.1.18	reserved

192.168.1.19	reserved

192.168.1.20	reserved

etc....



Work Book B, C, D etc...

column A	column B		column c

IPAddress	Server			Comment	

192.168.1.2	\MTT02PL1N17201	Xerox DocuPrint N17 PCL5e

192.168.1.3	\MTT02PL1N17202	Xerox DocuPrint N17 PCL5e

192.168.1.4	\MTT02PL1N17203	Xerox DocuPrint N17 PCL5e	

192.168.1.7	\MTT02PL2OP201	Lexmark Optra SC 1275

192.168.1.8	\MTT02PL2OP202	Lexmark Optra SC 1276

192.168.1.9	\MTT02PL2OP203	Lexmark Optra SC 1277

192.168.1.11	\MTT03PL1OP204	Lexmark Optra S 1625

192.168.1.13	\MTT03PL2N32204	Xerox DocuPrint N24/N32/N40

192.168.1.15	\MTT02PL2N32205	Xerox DocuPrint N24/N32

192.168.1.16	\MTT03PL3C41101	Xerox DocuPrint C410 PS

192.168.1.17	\MTT02PL3HP5102	HP LaserJet 5000 Series PS

192.168.1.18	\MTT03PL3HP6103	HP LaserJet 6000 Series PS	

etc....



Data merged into the First Work Book

column A	column B	column c		column d	column e	column f

IPAddress	Server		Comment			IPAddress	Server		Comment

192.168.1.1	reserved				192.168.1.30	reserved

192.168.1.2	02PL1N17201	Xerox N17		192.168.1.31	reserved

192.168.1.3	02PL1N17202	Xerox N18		etc...

192.168.1.4	02PL1N17203	Xerox N19

192.168.1.5	Joker	

192.168.1.6	Vader	

192.168.1.7	02PL2OP201	Optra SC 1275

192.168.1.8	02PL2OP202	Optra SC 1276

192.168.1.9	02PL2OP203	Optra SC 1277

192.168.1.10	Falcon	

192.168.1.11	03PL1OP204	Optra S 1625

192.168.1.12	turner	

192.168.1.13	03PL2N32204	Xerox N24/N32/N40

192.168.1.14	reserved	

192.168.1.15	03PL2N32205	Xerox N24/N32

192.168.1.16	03PL3C41101	Xerox C410 

192.168.1.17	02PL3HP5102	HP 5000 

192.168.1.18	03PL3HP6103	HP 6000 

192.168.1.19	reserved	

192.168.1.20	reserved

etc....	

[RET79]

I would recommend you take a look at Data-Get External data-new database query.

i.e. I think that merging data can be done well with Microsoft Query, perhaps this is what you need to get started. Give it a try, there is a good help thing there for it too. Let us know how you get on.

HTH

RET79