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!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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
54,315
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
54,315
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,989
Messages
5,834,737
Members
430,315
Latest member
bobh63

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