Excel Flip/concatenation

Find out why thousands trust the EE community with their toughest problems.

gowflow

5/14/2020

welll …. yes and no !!!

On your worksheet you have labels until label20 and then you have manufacturer and model

In your example
Noun
Modifier
Type
Electrical Rating
Manufacturer
Number

YOu have manufacturer at col 5.

I think you need to relax and follow me here.

We can get you what you want provided we have a sample of your data. Then basis what you just said we will suggest a format and will take it from there.

I can break all the PO into separate columns and say at col 21 and after we get the manufacturer there etc...
But first I need a smaple data.

As in the example you just put there is no trace of

contact
action
contact
reset
terminal
mount
…

on 31402 item we have something different
Type
voltage
pole
amperage
connection
interrupt

…. All these I guess you want to see them in different columns right ???
SO for this we need the data.
Gowflow

appelonia21

5/14/2020

ASKER

Yes, that is my point, the "lables", change based on the item name, which is the first line, 'relay', 'overload'. Which is why I made them generic labels, where no matter what the label is, the info in each row following the (:) gets put in order to fill up the labels columns. We can forget about all these labels if I can somehow get the Manufacturer info parsed out, and yes these are into separate columns.

gowflow

5/14/2020

ok so you don't really care for the data to be aligned in the column !!!
This is going to be a mess ….

Do you have this many labels ??? I can get you something nice if you give me a sample data. trust. me
Gowflow

Your help has saved me hundreds of hours of internet surfing.

fblack61

appelonia21

5/14/2020

ASKER

I attached the sample data of approx 1500 items. Did not that work or do you need something different? I do not know the max amount of labels that there would be, I assumed 20 in my first post. We can exclude all labels and except for the manufacturer info and I need that info aligned with the corresponding item that it references. so on ITEM 31327, I need the reference to the below in another column

KLOCKNER MOELLER:ZM-10 PKZ2

gowflow

5/14/2020

Try to zip the file.
Gowflow

Saqib Husain

5/14/2020

If you have Excel 365 subscription then
enter this formula in Cell A2 of sheet2

=UNIQUE(Sheet1!A3:A42)

Then enter this formula in B2 and then copy it down and across

=IF(COLUMN()<7,CHOOSE(COLUMN(),,MATCH($A2,Sheet1!$A:$A,0),COUNTIF(Sheet1!$A:$A,$A2),INDEX(Sheet1!$B:$B,$B2),LEFT(INDEX(Sheet1!$C:$C,$B2),FIND(",",INDEX(Sheet1!$C:$C,$B2))-1),REPLACE(INDEX(Sheet1!$C:$C,$B2),1,FIND(",",INDEX(Sheet1!$C:$C,$B2)),"")),IF(COLUMNS(B2:$G2)<$C2,REPLACE(INDEX(Sheet1!$C:$C,$B2+COLUMNS(B2:$G2)),1,FIND(":",INDEX(Sheet1!$C:$C,$B2+COLUMNS(B2:$G2))),""),""))

Find out why thousands trust the EE community with their toughest problems.

Louis LIETAER

5/14/2020

I won't use a vba/Excel approach for 1 million rows. I will export to text file, and apply a script (python power shell etc)

Saqib Husain

5/14/2020

New formula for the second file

=TRIM(IF(COLUMN()<7,CHOOSE(COLUMN(),,MATCH($A2,Sheet1!$A:$A,0),COUNTIF(Sheet1!$A:$A,$A2),INDEX(Sheet1!$B:$B,$B2),LEFT(INDEX(Sheet1!$C:$C,$B2)&",",FIND(",",INDEX(Sheet1!$C:$C,$B2)&",")-1),REPLACE(INDEX(Sheet1!$C:$C,$B2),1,FIND(",",INDEX(Sheet1!$C:$C,$B2)&","),"")),IF(COLUMNS(B2:$G2)<$C2,REPLACE(INDEX(Sheet1!$C:$C,$B2+COLUMNS(B2:$G2)),1,FIND(":",INDEX(Sheet1!$C:$C,$B2+COLUMNS(B2:$G2))),""),"")))

gowflow

5/14/2020

Here is my solution.
I cannot guarantee the behavior as very limited data sample but on what you provided it give the desired result.

Open the file and make sure macros are enabled and run the macro GetPO it will display results in sheet Result. To test it on your data make sure your data sits in sheet Sheet1. But for sure it is not final as for 1000000 records you will feel that the macro went into coma but in fact it is working. We need more data to build something workable.

Check if it is ok.

here is the code

Option Explicit

Sub GetPO()
Dim WS As Worksheet, WSR As Worksheet
Dim MaxRow As Long, MaxRowR As Long, I As Long, lCol As Long
Dim sItem As String, sManufact As String
Dim vItem As Variant, vHeader(27) As Variant


'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With


Set WS = Sheets("Sheet1")
MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
Set WSR = Sheets("Results")
MaxRowR = 1

'---> Clean Results
WSR.Cells.Delete
WSR.Range("A1:AB1") = Array("Item", "Short", "Noun", "Modifier", "Type", "Electrical Rating", "Label1", "Label2", "Label3", "Label4", "Label5", "Label6", "Label7", "Label8", "Label9", "Label0", "Label1", "Label2", "Label3", "Label4", "Label5", "Label6", "Label7", "Label8", "Label9", "Labe20", "Manufacturer", "Part Number")

For I = 3 To MaxRow
    If WS.Cells(I, "A") = sItem Then
        If InStr(1, WS.Cells(I, "C"), "MANUFACTURER") <> 0 Then
            sManufact = WS.Cells(I, "C")
        ElseIf sManufact = "" Then
            vItem = Split(WS.Cells(I, "C"), ":")
            WSR.Cells(MaxRowR, lCol) = Left(Trim(vItem(1)), Len(Trim(vItem(1))))
            lCol = lCol + 1
        ElseIf sManufact <> "Yes" Then
            vItem = Split(WS.Cells(I, "C"), ":")
            WSR.Cells(MaxRowR, "AA") = Trim(vItem(0))
            WSR.Cells(MaxRowR, "AB") = Left(Trim(vItem(1)), Len(Trim(vItem(1))))
            sManufact = "Yes"
        End If
    Else
        MaxRowR = MaxRowR + 1
        sItem = WS.Cells(I, "A")
        WSR.Cells(MaxRowR, "A") = WS.Cells(I, "A")
        WSR.Cells(MaxRowR, "B") = WS.Cells(I, "B")
        vItem = Split(WS.Cells(I, "C"), ",")
        WSR.Cells(MaxRowR, "C") = Trim(vItem(0))
        WSR.Cells(MaxRowR, "D") = Left(Trim(vItem(1)), Len(Trim(vItem(1))) - 1)
        lCol = 5
        sManufact = ""
    End If
    
Next I

WSR.UsedRange.EntireColumn.AutoFit
MsgBox "A total of " & MaxRowR & " Rows has been created and split in sheet Results", vbInformation, "Get Po's"

With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With



End Sub

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

It is imbedded in this workbook.
Gowflow
flip--1-V01.xlsm

rwheeler23

appelonia21

5/14/2020

ASKER

sample-2.xlsx

Saqib Husain

5/14/2020

Which solution are you using? VBA or Formula?

Saqib Husain

5/14/2020

If you are using the formulas then here is an update.

=TRIM(IF(COLUMN()<7,CHOOSE(COLUMN(),,MATCH($A2,Sheet1!$A:$A,0),COUNTIF(Sheet1!$A:$A,$A2),INDEX(Sheet1!$B:$B,$B2),LEFT(INDEX(Sheet1!$C:$C,$B2)&",",FIND(",",INDEX(Sheet1!$C:$C,$B2)&",")-1),REPLACE(INDEX(Sheet1!$C:$C,$B2),1,FIND(",",INDEX(Sheet1!$C:$C,$B2)&","),"")),IF(COLUMNS(B2:$G2)<VALUE($C2),REPLACE(INDEX(Sheet1!$C:$C,$B2+COLUMNS(B2:$G2)),1,FIND(":",INDEX(Sheet1!$C:$C,$B2+COLUMNS(B2:$G2))),""),"")))

Find out why thousands trust the EE community with their toughest problems.

Louis LIETAER

5/14/2020

Which solution are you using? VBA or Formula? or any script language ?

gowflow

5/14/2020

Here it is
Check it some items don't have manufacturer we can fintune and add vendor if you want.

Anyway here it is.
Gowflow
flip--1-V02.xlsm

appelonia21

5/14/2020

ASKER

I haven't been able to get any of the solutions to work so far. I think we need to expand to have the vendors. My excel keeps freezing

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

gowflow

5/14/2020

What do you mean none of the solution work ?? Have you tried the workbook I posted ??? not your data but last workbook.

ok here is with the vendor !!! for sure it will freeze. I need more of your data to make sure we cover everything try this workbook that I post not your data and if all ok then will worry about your data it need a different setup.
Gowflow
flip--1-V03.xlsm

Saqib Husain

5/14/2020

I haven't been able to get any of the solutions to work so far.

You should post a file showing what is "Not working". It is difficult to guess what is your problem unless you show it to us.

Louis LIETAER

5/14/2020

I haven't been able to get any of the solutions to work so far. I think we need to expand to have the vendors. My excel keeps freezing

reminder : I won't use a vba/Excel approach for 1 million rows. I will export to text file, and apply a script (python power shell etc)

Find out why thousands trust the EE community with their toughest problems.

ASKER CERTIFIED SOLUTION

gowflow

5/14/2020

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

appelonia21

5/14/2020

ASKER

It ran fine on the sample data sheet, but when I brought in additional items I got this error:

WSR.Cells(MaxRowR, lCol) = Left(Trim(vItem(1)), Len(Trim(vItem(1))))

appelonia21

5/14/2020

ASKER

I can export to a text file, but I am not certain what to do from that point?

gowflow

5/14/2020

Please post the data that you added I cannot guess. Need to troubleshoot. As I said in my post maybe you haven't seen it as it crossed I duplicated your data for 1,000,000 records and it ran in 25 seconds. So we need to see what you have that does not match the code.
Gowflow

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Saqib Husain

5/14/2020

Did you try the formulas? If yes, what happened?

appelonia21

5/14/2020

ASKER

I did, it worked for the item number and for the first few in the PO text but did not get to the mfg data information that I need.

Louis LIETAER

5/14/2020

@gowflow, 25'' yes well done :-), and sure I will exclude any formula solution

Find out why thousands trust the EE community with their toughest problems.

Louis LIETAER

5/14/2020

I can export to a text file, but I am not certain what to do from that point?

You will have to choose which type of script language to use :-)

gowflow

5/14/2020

Hello again
I noted some instance where the code would hang for different reasons some items have = so interpreted as a formulas. Some where we expect a , or : doesn't have and some are blank …

All these are fixed here is the new code.

Option Explicit

Sub GetPO()
Dim WS As Worksheet, WSR As Worksheet
Dim MaxRow As Long, MaxRowR As Long, I As Long, lCol As Long
Dim sItem As String, sManufact As String, sVendor As String
Dim vItem As Variant, vHeader(27) As Variant


'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With


Set WS = Sheets("Sheet1")
MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
Set WSR = Sheets("Results")
MaxRowR = 1

'---> Clean Results
WSR.Cells.Delete
WSR.Range("A1:AD1") = Array("Item", "Short", "Noun", "Modifier", "Type", "Electrical Rating", "Label1", "Label2", "Label3", "Label4", "Label5", "Label6", "Label7", "Label8", "Label9", "Label0", "Label1", "Label2", "Label3", "Label4", "Label5", "Label6", "Label7", "Label8", "Label9", "Labe20", "Manufacturer", "Part Number", "Vendor", "Part Number")

For I = 2 To MaxRow
    If WS.Cells(I, "A") = sItem Then
        If InStr(1, WS.Cells(I, "C"), "MANUFACTURER") <> 0 Then
            sManufact = WS.Cells(I, "C")
        ElseIf InStr(1, WS.Cells(I, "C"), "VENDOR") <> 0 Then
            sVendor = WS.Cells(I, "C")
        ElseIf sManufact = "" And sVendor = "" Then
            If InStr(1, WS.Cells(I, "C"), ":") <> 0 Then
                vItem = Split(WS.Cells(I, "C"), ":")
                If UBound(vItem) = 1 Then
                    vItem(1) = Replace(vItem(1), "=", "")
                    WSR.Cells(MaxRowR, lCol) = Left(Trim(vItem(1)), Len(Trim(vItem(1))))
                End If
            Else
                WSR.Cells(MaxRowR, lCol) = Trim(WS.Cells(I, "C"))
            End If
            lCol = lCol + 1
        ElseIf sManufact <> "Yes" Then
            vItem = Split(WS.Cells(I, "C"), ":")
            WSR.Cells(MaxRowR, "AA") = Trim(vItem(0))
            If UBound(vItem) = 1 Then
                WSR.Cells(MaxRowR, "AB") = Left(Trim(vItem(1)), Len(Trim(vItem(1))))
            End If
            sManufact = "Yes"
        ElseIf sVendor <> "Yes" Then
            vItem = Split(WS.Cells(I, "C"), ":")
            WSR.Cells(MaxRowR, "AC") = Trim(vItem(0))
            If UBound(vItem) = 1 Then
                WSR.Cells(MaxRowR, "AD") = Left(Trim(vItem(1)), Len(Trim(vItem(1))))
            End If
            sVendor = "Yes"
        End If
    Else
        MaxRowR = MaxRowR + 1
        sItem = WS.Cells(I, "A")
        WSR.Cells(MaxRowR, "A") = WS.Cells(I, "A")
        WSR.Cells(MaxRowR, "B") = WS.Cells(I, "B")
        If InStr(1, WS.Cells(I, "C"), ",") <> 0 Then
            vItem = Split(WS.Cells(I, "C"), ",")
            WSR.Cells(MaxRowR, "C") = Trim(vItem(0))
            WSR.Cells(MaxRowR, "D") = Left(Trim(vItem(1)), Len(Trim(vItem(1))) - 1)
        Else
            If Trim(WS.Cells(I, "C")) <> "" Then WSR.Cells(MaxRowR, "C") = Left(Trim(WS.Cells(I, "C")), Len(Trim(WS.Cells(I, "C"))) - 1)
        End If
        
        lCol = 5
        sManufact = ""
        sVendor = ""
    End If
    
Next I

WSR.UsedRange.EntireColumn.AutoFit
MsgBox "A total of " & MaxRowR & " Rows has been created and split in sheet Results", vbInformation, "Get Po's"

With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With



End Sub