We help IT Professionals succeed at work.
Get Started

Excel Flip/concatenation

111 Views
Last Modified: 2020-05-14
I have an excel file of approx 1 million rows.  There are only three columns with the items differentiated by item numbers.  I need to somehow flip this data from so that I only have one row per item number with a separated columns per PO Text.   Below are two item numbers and their associated info.  Below that are the results that I need to get for the entire file.  Essentially I need one row for each item, extract the info in the PO Text with the first row to be the noun, modifier, and the rest of the information broken into labels. I need the info separated by : into the label values, I do not need to keep the 'Type', ' electrical rating', etc, just the values for that label.  I also need everything in the FABRICANTE (MANUFACTURER): fields broken into the Manufacturer, and the part number, again separated by the :

If this is too difficult then essentially what I need only need the item number, short description and the FABRICANTE (MANUFACTURER): information broken out.  I don't need all of the label info if that is too difficult?

Is this possible or is there another way I can display this information to show what I need?

Item      Short Description      PO Text
31327      RELAY,O/L:THERMAL,6-10/80-140A 115/575V      RELAY,OVERLOAD:
31327      RELAY,O/L:THERMAL,6-10/80-140A 115/575V           TYPE: THERMAL
31327      RELAY,O/L:THERMAL,6-10/80-140A 115/575V           ELECTRICAL RATING: 6-10/80-140A 115/575V
31327      RELAY,O/L:THERMAL,6-10/80-140A 115/575V           CONTACT ARRANGEMENT: *****
31327      RELAY,O/L:THERMAL,6-10/80-140A 115/575V           ACTION: *****
31327      RELAY,O/L:THERMAL,6-10/80-140A 115/575V           CONTACT:  *****
31327      RELAY,O/L:THERMAL,6-10/80-140A 115/575V           RESET: *****
31327      RELAY,O/L:THERMAL,6-10/80-140A 115/575V           TERMINAL:  *****
31327      RELAY,O/L:THERMAL,6-10/80-140A 115/575V           MOUNT:  *****
31327      RELAY,O/L:THERMAL,6-10/80-140A 115/575V           ENCLOSURE:  *****
31327      RELAY,O/L:THERMAL,6-10/80-140A 115/575V      TEXTO (TEXT):FRECUENCIA: 50-60HZ
31327      RELAY,O/L:THERMAL,6-10/80-140A 115/575V      FABRICANTE (MANUFACTURER):
31327      RELAY,O/L:THERMAL,6-10/80-140A 115/575V      KLOCKNER MOELLER:ZM-10 PKZ2
31328      RELAY,O/L:THRM 6-16/130-220A 115/575V      RELAY,OVERLOAD:
31328      RELAY,O/L:THRM 6-16/130-220A 115/575V           TYPE:THERMAL
31328      RELAY,O/L:THRM 6-16/130-220A 115/575V           ELECTRICAL RATING:6-16/130-220A 115/575V
31328      RELAY,O/L:THRM 6-16/130-220A 115/575V           CONTACT ARRANGEMENT:*****
31328      RELAY,O/L:THRM 6-16/130-220A 115/575V           ACTION:*****
31328      RELAY,O/L:THRM 6-16/130-220A 115/575V           CONTACT:*****
31328      RELAY,O/L:THRM 6-16/130-220A 115/575V           RESET:*****
31328      RELAY,O/L:THRM 6-16/130-220A 115/575V           TERMINAL:*****
31328      RELAY,O/L:THRM 6-16/130-220A 115/575V           MOUNT:*****
31328      RELAY,O/L:THRM 6-16/130-220A 115/575V           ENCLOSURE:*****
31328      RELAY,O/L:THRM 6-16/130-220A 115/575V      TEXTO (TEXT):FRECUENCIA: 50-60HZ
31328      RELAY,O/L:THRM 6-16/130-220A 115/575V      FABRICANTE (MANUFACTURER):
31328      RELAY,O/L:THRM 6-16/130-220A 115/575V      MOELLER:ZM-16 PKZ2

Results:
Item      Short Description      Noun      Modifier      Label1      Label2      Label2      Label3      Label4      Label5      Label6      Label7            Manufacturer      Part Number
31327      RELAY,O/L:THERMAL,6-10/80-140A 115/575V      RELAY      OVERLOAD      THERMAL      6-10/80-140A 115/575V      *****      *****      *****      *****      *****                                                                                            KLOCKNER MOELLER      ZM-10 PKZ2
31328      RELAY,O/L:THRM 6-16/130-220A 115/575V      RELAY      OVERLOAD      THERMAL      6-16/130-220A 115/575V      *****      *****      *****      *****      *****                                                                                          KLOCKNER MOELLER      ZM-16 PKZ2
flip--1-.xlsx
Comment
Watch Question
Partner
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 47 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE