• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Converting uneven (multiple rows of data) into 1 row

A

angelams

New Member
I have a list of Spid numbers and a service category identifying if it is a Water Spid or a Waste spid.The Spid is split into 2 parts. The Core is the identifier and the Category states if it is a Water or a Waste spid.

If the 1st digit in the Category is a 1 = Water.

If the 1st digit in the Category is a 2 = Waste.

There may only be a water spid or a property may have a water and waste spid.
What I want to do is have the information on 1 row rather than 2 rows

This is the current shape of the data - but I want it one row

SPID Core Category Service Category
100000000151 100000000 151 1
100000020101 100000020 101 1
100000020251 100000020 251 2
100000030109 100000030 109 1
100000030206 100000030 206 2
100000040104 100000040 104 1
100000040201 100000040 201 2
100000050152 100000050 152 1
100000050209 100000050 209 2


Core spid Water Waste
100000020 100000020101 100000020251
 
Please try this:

Code: 
=IF(SUM((LEFT($A$2:$A2,9)=LEFT($A2,9))*1)=1,IFERROR(INDEX($A$2:$A$9,SMALL(IFERROR(MID(IF(LEFT($A$2:$A$9,9)=LEFT($A2,9),$A$2:$A$9,FALSE),10,1)*ROW($A$1:$A$8),FALSE),COLUMN(A16))),"--"),"...")

with CSE
 
Welcome to the forum angelams!

Please see attached which contains sample data and instructions on how to convert your data into desired layout.
 

Attachments

  • Data Change.xlsx
    13.4 KB · Views: 4
angelams said:
I have a list of Spid numbers and a service category identifying if it is a Water Spid or a Waste spid.The Spid is split into 2 parts. The Core is the identifier and the Category states if it is a Water or a Waste spid.

If the 1st digit in the Category is a 1 = Water.

If the 1st digit in the Category is a 2 = Waste.

There may only be a water spid or a property may have a water and waste spid.
What I want to do is have the information on 1 row rather than 2 rows

This is the current shape of the data - but I want it one row

SPID Core Category Service Category
100000000151 100000000 151 1
100000020101 100000020 101 1
100000020251 100000020 251 2
100000030109 100000030 109 1
100000030206 100000030 206 2
100000040104 100000040 104 1
100000040201 100000040 201 2
100000050152 100000050 152 1
100000050209 100000050 209 2


Core spid Water Waste
100000020 100000020101 100000020251
Click to expand...
 
Thank you so much for your quick responses. That has been a great help
I love the 2 different ways to handle that query
 
You must log in or register to reply here.
Back
Top