Data to Column, remove unwanted fields

Hello

I have long list of data saved in word, i want to copy it to excel and with the help of Macro, i want to convert it data to column, but remove some duplicate fields and have same data in one column.

i have created a macro with steps records, however some of the fields have multiple entries and due to which the row has additional fields, now i want to remove those additional fields, so that i have one data type in one column only, please see the attached word and excel file for example

below is the code from VBA editor

Sub Macro1()
'
' Macro1 Macro
'

'
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=False, Other:=True, OtherChar:= _
":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1) _
, Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array( _
19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), _
Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array( _
32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), _
Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1)), _
TrailingMinusNumbers:=True
Range("A1").Select
End Sub

now i want to remove the additional name, or do something that same type data is in same column

With one record from the Word file (e.g. the first one) can you show the result you want in Excel

Is the list of headers shown in your example, the one you want ?
In other words, there is not the same list of data in all records: Record 3 has 2 allias

A possibility could be:
1 - You copy paste the data from Word to an "working sheet" in Excel*
2 - You launch next macro
The first record is used to prepare the list of items

Option Explicit
Option Base 1

Sub Act1()
Const StStr = "%"
Const EndStr = "µ"
Dim WkAr
Dim WkRg  As Range
Dim I  As Long, II As Long, J As Long, JJ As Long
Dim Re As Integer
Dim T, TT, K
Dim HdDic   As Object
Set HdDic = CreateObject("Scripting.Dictionary")

    On Error Resume Next
    Columns(1).Select  '.SpecialCells(xlCellTypeBlanks).EntireRow.DeleteSomething
    Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Set WkRg = Cells(1, 1).CurrentRegion
    WkAr = WkRg: WkRg.ClearContents
    For I = LBound(WkAr, 1) To UBound(WkAr, 1)
        Re = 1   '   not 0
        While Re <> 0
            Re = InStr(WkAr(I, 1), ":")
            If Re <> 0 Then
                WkAr(I, 1) = Replace(WkAr(I, 1), ":", "%", 1, 1)
                T = InStrRev(WkAr(I, 1), ",", Re)
                WkAr(I, 1) = Left(WkAr(I, 1), T - 1) & "µ" & Mid(WkAr(I, 1), T + 1)
            End If
        Wend
    Next I

    For J = 0 To UBound(Split(WkAr(1, 1), "%"), 1) - 1
        K = Split(WkAr(1, 1), "%")(J)
        T = Split(K, "µ")
        TT = T(UBound(T, 1))
        Cells(1, J + 1) = TT
        HdDic.Item(TT) = J + 1
    Next J
    
    Dim ResAr
    ReDim ResAr(1 To UBound(WkAr, 1), 1 To J)
    II = 1
    For I = LBound(WkAr, 1) To UBound(WkAr, 1)
        For Each K In Split(WkAr(I, 1), "µ")
            TT = Split(K, "%")(0)
            If HdDic.exists(TT) Then
                JJ = HdDic.Item(TT)
                T = Split(K, "%")(1)
                ResAr(II, JJ) = T
            End If
        Next K
        II = II + 1
    Next I
    Cells(2, 1).Resize(UBound(ResAr, 1), UBound(ResAr, 2)) = ResAr
    
End Sub

What are your comments about the macro sent, is it doing the job, if not give details

OK
In the file attached in sheet "working Sheet" paste the data from the Word file.
Then, while you are in "Working Sheet" do control + a to launch macro "Act1"

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

crossposted: https://chandoo.org/forum/threads/da...-fields.47894/

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future

First, we do not delete posts as this is a large data base that is searched regularly by many and any posts may be of help to others. Secondly, we do not forbid crossposting, we expect you to read the rules and understand that we expect you to let us know when you crosspost. This is standard practice across most Excel Forums including Chandoo. If you read our rules and theirs, you will understand better this request.