Help with merging and sorting Data

Staticman

New Member
Joined
Mar 15, 2002
Messages
7
<pre>
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....
</pre>
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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
Back
Top