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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
... 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?
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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