trying to figure out the formula to rearrange the data

73345923459

New Member
Joined
Sep 5, 2014
Messages
4
Hi All
I have some data in Excel 2010 that I would like to rearrange but I am struggling to figure out how to do this. The data looks like this



IP Address192.168.1.1
MAC Address00:00:00:00:00:00
VendorMicrosoft
Host Nameserver.domain.local
StateDevice is Up
First Seen01 September 2014
NetBIOS NameSERVER
Domain ControllerYes
File ServerYes
IP Address192.168.1.2
MAC Address00:00:00:00:00:00
VendorMicrosoft
Host Nameserver.domain.local
StateDevice is Up
First Seen01 September 2014
NetBIOS NameSERVER
Domain ControllerYes
File ServerYes

<tbody>
</tbody>


and it continues on but not every section has all the same as above but they all have IP Address, MAC Address and Vendor.


What I am trying to do is change it so that I have


IP AddressMAC AddressVendorHost NameNetBIOS NameDomain ControllerFile Server
192.168.1.100:00:00:00:00Microsoftserver.localSERVERYESYes
192.168.1.200:00:00:00:00Microsoftserver.localSERVERYESYes

<tbody>
</tbody>

I have tried a number of formulas but I am stuck and need a fresh pair of eyes to help me in the right direction. Is there a formula or a number of formulas that can help me populate the data into multiple columns?


Many thanks for your help in advance!
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

73345923459

New Member
Joined
Sep 5, 2014
Messages
4
Hi Rick and Andrew... thanks for deleting the previous thread. I switched to IE from Firefox and managed to past the excel data into the post. I did lose the borders though so I hope my post is okay.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
I know you where specifically asking for a formula , but out of interest I have written this code that pastes your required result starting "F1".
If you cannot use it please ignore this code.
Code:
[COLOR=Navy]Sub[/COLOR] MG05Sep21
[COLOR=Navy]Dim[/COLOR] oHds [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Q [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    oHds = Array("IP Address", "MAC Address", "Vendor", "Host Name", "State", "First Seen", "NetBIOS Name", "Domain Controller", "File Server")
        Range("F1").Resize(, UBound(oHds) + 1) = oHds
            [COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
                .CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] n = 0 To UBound(oHds): .Item(oHds(n)) = Array(n, 2): [COLOR=Navy]Next[/COLOR]
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
        [COLOR=Navy]If[/COLOR] .exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
            Q = .Item(Dn.Value)
                Cells(Q(1), 6).Offset(, Q(0)).Value = Dn.Offset(, 1).Value
                Q(1) = Q(1) + 1
           .Item(Dn.Value) = Q
        [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
 

73345923459

New Member
Joined
Sep 5, 2014
Messages
4
Hi Mick

That's fantastic and thank you. I'll give this a go once I figure out how to run the code...
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841

ADVERTISEMENT

Two things ,
1) I think the code could have problems , so I've altered it, below.
The code now assumes that for each set of data there will always be a line "IP Address", this is used as a marker to go to the next row.
2) Method to run code shown below.

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.


On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.

Code:
[COLOR="Navy"]Sub[/COLOR] MG06Sep17
[COLOR="Navy"]Dim[/COLOR] oHds [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] nRw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
nRw = 1
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    oHds = Array("IP Address", "MAC Address", "Vendor", "Host Name", "State", "First Seen", "NetBIOS Name", "Domain Controller", "File Server")
        Range("F1").Resize(, UBound(oHds) + 1) = oHds
            [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
                .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(oHds): .Item(oHds(n)) = n: [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            nRw = nRw + IIf(Dn.Value = "IP Address", 1, 0)
            Cells(nRw, 6).Offset(, .Item(Dn.Value)).Value = Dn.Offset(, 1).Value
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
... not every section has all the same as above but they all have IP Address, MAC Address and Vendor.
If a section is missing, say, 'State', is the whole row completely missing or does the first column still say 'State' and the second column is just empty?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If a section is missing, say, 'State', is the whole row completely missing ...
I've assumed the above for now.
My suggested code is:
Rich (BB code):
Sub Rearrange()
  Dim a, b, Hdrs
  Dim i As Long, k As Long, rws As Long, pos As Long
  Dim s As String

  Hdrs = Array("IP Address", "MAC Address", "Vendor", "Host Name", "State", "First Seen", "NetBIOS Name", "Domain Controller", "File Server")
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
  rws = UBound(a, 1)
  ReDim b(1 To rws, 1 To 9)
  For i = 1 To rws
    s = a(i, 1)
    If Len(s) Then
      If s = "IP Address" Then
        k = k + 1
        b(k, 1) = a(i, 2)
      Else
        pos = Application.Match(s, Hdrs, False)
        b(k, pos) = a(i, 2)
      End If
    End If
  Next i
  Range("F2").Resize(k, 9).Value = b
  Range("F1:N1").Value = Hdrs
End Sub



(I don't think a formula solution is feasible for this problem)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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
Top