Excel formula help needed please!

[adamhodgkins]

Hi all,

I have been given a task to transfer some data from one spreadsheet to another, but I'm struggling to find a formula to help with it...

The data I have to transfer is in the following format:

[TABLE="width: 360"]
<TBODY>[TR]
[TD]Server Name</SPAN>[/TD]
[TD]IP Address</SPAN>[/TD]
[TD]Area</SPAN>[/TD]
[/TR]
[TR]
[TD]Server A</SPAN>[/TD]
[TD]1.1.1.1</SPAN>[/TD]
[TD]Production</SPAN>[/TD]
[/TR]
[TR]
[TD]Server A</SPAN>[/TD]
[TD]2.2.2.2</SPAN>[/TD]
[TD]Development</SPAN>[/TD]
[/TR]
[TR]
[TD]Server A</SPAN>[/TD]
[TD]3.3.3.3</SPAN>[/TD]
[TD]Testing</SPAN>[/TD]
[/TR]
[TR]
[TD]Server B</SPAN>[/TD]
[TD]4.4.4.4</SPAN>[/TD]
[TD]Production</SPAN>[/TD]
[/TR]
[TR]
[TD]Server B</SPAN>[/TD]
[TD]5.5.5.5</SPAN>[/TD]
[TD]Production</SPAN>[/TD]
[/TR]
[TR]
[TD]Server B</SPAN>[/TD]
[TD]6.6.6.6</SPAN>[/TD]
[TD]Testing</SPAN>[/TD]
[/TR]
[TR]
[TD]Server C</SPAN>[/TD]
[TD]7.7.7.7</SPAN>[/TD]
[TD]Development</SPAN>[/TD]
[/TR]
[TR]
[TD]Server C</SPAN>[/TD]
[TD]8.8.8.8</SPAN>[/TD]
[TD]Testing</SPAN>[/TD]
[/TR]
[TR]
[TD]Server C</SPAN>[/TD]
[TD]9.9.9.9</SPAN>[/TD]
[TD]Production</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=2></COLGROUP>[/TABLE]

The spreadsheet that needs this information adding to is in a different format:

[TABLE="width: 819"]
<TBODY>[TR]
[TD]Server Name</SPAN>[/TD]
[TD]IP Address 1</SPAN>[/TD]
[TD]Area 1</SPAN>[/TD]
[TD]IP Address 2</SPAN>[/TD]
[TD]Area 2</SPAN>[/TD]
[TD]IP Address 3</SPAN>[/TD]
[TD]Area 3</SPAN>[/TD]
[/TR]
[TR]
[TD]Server A</SPAN>[/TD]
[TD]1.1.1.1</SPAN>[/TD]
[TD]Production</SPAN>[/TD]
[TD]2.2.2.2</SPAN>[/TD]
[TD]Development</SPAN>[/TD]
[TD]3.3.3.3</SPAN>[/TD]
[TD]Testing</SPAN>[/TD]
[/TR]
[TR]
[TD]Server B</SPAN>[/TD]
[TD]4.4.4.4</SPAN>[/TD]
[TD]Production</SPAN>[/TD]
[TD]5.5.5.5</SPAN>[/TD]
[TD]Production</SPAN>[/TD]
[TD]6.6.6.6</SPAN>[/TD]
[TD]Testing</SPAN>[/TD]
[/TR]
[TR]
[TD]Server C</SPAN>[/TD]
[TD]7.7.7.7</SPAN>[/TD]
[TD]Development</SPAN>[/TD]
[TD]8.8.8.8</SPAN>[/TD]
[TD]Testing</SPAN>[/TD]
[TD]9.9.9.9</SPAN>[/TD]
[TD]Production</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=6></COLGROUP>[/TABLE]

If it was only a few entries I'd be happy to do it manually, but there are just under 15,000 rows of data that need to be transferred so I'd like to try and automate this if at all possible?

I've tried using VLOOKUP but that will stop when it finds the first instance of 'Server Name' and not capture the other information listed.

Any help with this would be greatly appreciated!

Thanks

Adam

 

[Jubjab]

Do all servers always have 3 IP addresses and areas, or can it differ? Does it have to be formula based or is a macro ok?

 

[Pat_Mc_64]

Adam,
I've created a converter spreadsheet for you however, I'm blocked from publishing it from my office.
The spreadsheet simply uses Countifs, VLOOKUPS, IFERRORs, and you set the number of IPs (columns) and the formulas do the rest.
All you need to do is 1) paste your data starting in cell C3, 2) Look at Cell C1 for MAX IP addesses on Server and ensure the columns accommodate that many.
3) Copy the formulas in row 5 to as many rows as there is data
4) Auto filter on column G to remove the 'blank' rows and you have what you need.
Before sending, what is the MAX IP addresses on a Server? (so I can setup it up so there is no work for you).
I can send it to you separately and then you cna publish so others can use it, and see the techniques used.

 

[adamhodgkins]

Hi Jubjab,

The servers have between 1 and 20 IP addresses associated with them, it doesn't need to be formula based, if there is a macro way of sorting this out that would be fantastic!

Thanks

Adam

 

[adamhodgkins]

Hi Pat_Mc_64,

Unfortunately due to company rules they will not accept attachments from unknown people...

Is there any chance you could maybe post the formulas in a reply? The max number of IP addresses that a server has is 20, but the majority have 3 or 4.

Thanks

Adam

 

[Pat_Mc_64]

Note: Formulas from Row 4 and Below are the same so you can copy them down to as many rows as there is data (~1500 you said)

Starting From Row 1 to row 4 (except where you sample data differs I put to row 12 column C,D,E)

Column A
[TABLE="width: 155"]
<TBODY>[TR]
[TD]row 1 blank</SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[/TR]
[TR]
[TD]Lookup</SPAN>[/TD]
[/TR]
[TR]
[TD]=C4&"-"&B4</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]


Column B
[TABLE="width: 211"]
<TBODY>[TR]
[TD]MAX</SPAN>[/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[/TR]
[TR]
[TD]IP Count Helper</SPAN>[/TD]
[/TR]
[TR]
[TD]=COUNTIF($C$3:C4,C4)</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]


Column C
[TABLE="width: 175"]
<TBODY>[TR]
[TD]=MAX(B:B)</SPAN>[/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[/TR]
[TR]
[TD]Server Name</SPAN>[/TD]
[/TR]
[TR]
[TD]Server A</SPAN>[/TD]
[/TR]
[TR]
[TD]Server A</SPAN>[/TD]
[/TR]
[TR]
[TD]Server A</SPAN>[/TD]
[/TR]
[TR]
[TD]Server B</SPAN>[/TD]
[/TR]
[TR]
[TD]Server B</SPAN>[/TD]
[/TR]
[TR]
[TD]Server B</SPAN>[/TD]
[/TR]
[TR]
[TD]Server C</SPAN>[/TD]
[/TR]
[TR]
[TD]Server C</SPAN>[/TD]
[/TR]
[TR]
[TD]Server C</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

Column D
[TABLE="width: 144"]
<TBODY>[TR]
[TD]Setup</SPAN>[/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[/TR]
[TR]
[TD]IP Address</SPAN>[/TD]
[/TR]
[TR]
[TD]1.1.1.1</SPAN>[/TD]
[/TR]
[TR]
[TD]2.2.2.2</SPAN>[/TD]
[/TR]
[TR]
[TD]3.3.3.3</SPAN>[/TD]
[/TR]
[TR]
[TD]4.4.4.4</SPAN>[/TD]
[/TR]
[TR]
[TD]5.5.5.5</SPAN>[/TD]
[/TR]
[TR]
[TD]6.6.6.6</SPAN>[/TD]
[/TR]
[TR]
[TD]7.7.7.7</SPAN>[/TD]
[/TR]
[TR]
[TD]8.8.8.8</SPAN>[/TD]
[/TR]
[TR]
[TD]9.9.9.9</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

Column E
[TABLE="width: 187"]
<TBODY>[TR]
[TD]=MAX(H1:EI1)</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[/TR]
[TR]
[TD]Area</SPAN>[/TD]
[/TR]
[TR]
[TD]Production</SPAN>[/TD]
[/TR]
[TR]
[TD]Development</SPAN>[/TD]
[/TR]
[TR]
[TD]Testing</SPAN>[/TD]
[/TR]
[TR]
[TD]Production</SPAN>[/TD]
[/TR]
[TR]
[TD]Production</SPAN>[/TD]
[/TR]
[TR]
[TD]Testing</SPAN>[/TD]
[/TR]
[TR]
[TD]Development</SPAN>[/TD]
[/TR]
[TR]
[TD]Testing</SPAN>[/TD]
[/TR]
[TR]
[TD]Production</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]


Column F - BLANK

Column G
[TABLE="width: 184"]
<TBODY>[TR]
[TD]Count</SPAN>[/TD]
[/TR]
[TR]
[TD]Column</SPAN>[/TD]
[/TR]
[TR]
[TD]Server</SPAN>[/TD]
[/TR]
[TR]
[TD]=IF(B4=1,C4,"")</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]


Column H
[TABLE="width: 184"]
<TBODY>[TR]
[TD]1</SPAN>[/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[/TR]
[TR]
[TD]=$D$3&" "&H1</SPAN>[/TD]
[/TR]
[TR]
[TD]=IFERROR(VLOOKUP($G4&"-"&H$1,$A$3:$E$12,H$2,FALSE),"")</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

Column I
[TABLE="width: 184"]
<TBODY>[TR]
[TD]1</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[/TR]
[TR]
[TD]=$E$3&" "&I1</SPAN>[/TD]
[/TR]
[TR]
[TD]
Column J
[TABLE="width: 184"]
<TBODY>[TR]
[TD]=H1+1</SPAN>[/TD]
[/TR]
[TR]
[TD]=$H$2</SPAN>[/TD]
[/TR]
[TR]
[TD]=$D$3&" "&J1</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

Column K
[TABLE="width: 184"]
<TBODY>[TR]
[TD]=I1+1</SPAN>[/TD]
[/TR]
[TR]
[TD]=$I$2</SPAN>[/TD]
[/TR]
[TR]
[TD]=$E$3&" "&K1</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]


Same formula in H4 is used for all the lookup ...


Repeat Column J & K for each additional instance of # of IPs
[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]