How to convert or transpose list into table?

This would require VBA programming, you may not be aware of the "automation" capabilities inside Excel.

If you can provide an example or two of the raw text files or the raw Excel files you start with, I can write a little routine and add it to a "button" to make it easy on you.

If they are text files, a few questions:

1) what is the folder path to where you store those text files?
2) Should the macro "clear" the table and make a clean new one each time, or add to the bottom of any existing data in the table?


To attach a sample workbook and text files - Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

The resulting table is a single string for each unit? Or is that separate cells in a row? Your sheet2 didn't have a mockup of what you really want to see.

Ideally the resulting table would have separate cells in a row something like this:

Column A                       Column B      Column C
ABBMcHSW01 (10.1.1.253):       Unit 1        Product serial number: YEFF6UH1F0840
ABBMcHSW01 (10.1.1.253):       Unit 2        Product serial number: YEFF75HD13AC0
ABIPBMTSW01 (10.1.1.121):      Unit 1        Product serial number: 9KXF9XMBF6140
ABIPCORESW01 (10.1.1.122):     Unit 1        Product serial number: 9KXF9XMBF5F00

where the information in Column A would be repeated if there was more than one switch in the stack

Struggling a bit with the smartest way to represent the data in the report. Once it is like this folks where I work with average Excel skills should be able to manipulate easier...

How about this?
Data Range

	A	B	C	D
1	Device	IP	Unit	Serial Number
2	ABBMcHSW01	10.1.1.253	1	YEFF6UH1F0840
3	ABBMcHSW01	10.1.1.253	2	YEFF75HD13AC0
4	ABIPBMTSW01	10.1.1.121	1	9KXF9XMBF6140
5	ABIPCORESW01	10.1.1.122	1	9KXF9XMBF5F00

The macro:

Option Explicit

Sub ImportDeviceInfo()
Dim LR As Long, Rw As Long, NR As Long
Dim wsIN As Worksheet, wsOUT As Worksheet
Dim Device As String, IP As String, Unit As String, MyArr As Variant

Set wsOUT = ThisWorkbook.Sheets("DataTable")
Set wsIN = ThisWorkbook.Sheets("Incoming")

NR = wsOUT.Range("A" & Rows.Count).End(xlUp).Row + 1
LR = wsIN.Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
For Rw = 1 To LR
    With wsIN.Range("A" & Rw)
        If Len(.Value) > 0 Then
            If InStr(.Value, "):") > 0 Then
                MyArr = Split(Trim(.Value), " ")
                Device = MyArr(0)
                IP = Replace(Replace(MyArr(1), "(", ""), "):", "")
            ElseIf InStr(.Value, "Unit") > 0 Then
                Unit = Trim(Replace(.Value, "Unit", ""))
            ElseIf InStr(.Value, "serial number") > 0 Then
                wsOUT.Range("A" & NR).Value = Device
                wsOUT.Range("B" & NR).Value = IP
                wsOUT.Range("C" & NR).Value = Unit
                wsOUT.Range("D" & NR).Value = Trim(Mid(.Value, InStr(.Value, "number:") + 8, 20))
                NR = NR + 1
            End If
        End If
    End With
Next Rw
Application.ScreenUpdating = True
wsOUT.Columns.AutoFit
End Sub

Here's a commented version of the macro to help with your examination:

Option Explicit

Sub ImportDeviceInfo()
Dim LR As Long, Rw As Long, NR As Long
Dim wsIN As Worksheet, wsOUT As Worksheet
Dim Device As String, IP As String, Unit As String, MyArr As Variant

Set wsOUT = ThisWorkbook.Sheets("DataTable")            'destination sheet
Set wsIN = ThisWorkbook.Sheets("Incoming")              'source data

NR = wsOUT.Range("A" & Rows.Count).End(xlUp).Row + 1    'next empty row on destination sheet
LR = wsIN.Range("A" & Rows.Count).End(xlUp).Row         'last used row of source data

Application.ScreenUpdating = False                      'speed up macro by not updating the screen
For Rw = 1 To LR                                        'evaluate one row at a time
    With wsIN.Range("A" & Rw)                           'looking in column A of that row
        If Len(.Value) > 0 Then                         'skip this if the cell is blank
            If InStr(.Value, "):") > 0 Then             'if a colon exists in the text string...
                MyArr = Split(Trim(.Value), " ")        '...split the string
                Device = MyArr(0)                       '...store the Device Name for use later
                IP = Replace(Replace(MyArr(1), "(", ""), "):", "")  '...store the IP, clean out the unneeded characters
            ElseIf InStr(.Value, "Unit") > 0 Then                   'if "unit" exists in the text string...
                Unit = Trim(Replace(.Value, "Unit", ""))            '...store the unit number for use later
            ElseIf InStr(.Value, "serial number") > 0 Then      'if "serial number" exists in the test string...
                wsOUT.Range("A" & NR).Value = Device    '...write the device name into empty row
                wsOUT.Range("B" & NR).Value = IP        '...write the IP into the empty row
                wsOUT.Range("C" & NR).Value = Unit      '...write the Unit number into the empty row
                wsOUT.Range("D" & NR).Value = Trim(Mid(.Value, InStr(.Value, "number:") + 8, 20))  '...write out the serial number clean out the unneeded characters
                NR = NR + 1                             'increment to next empty row
            End If
        End If
    End With
Next Rw
Application.ScreenUpdating = True                       'update the screen one time at the end
wsOUT.Columns.AutoFit                                   'clean up the columns
End Sub

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.