Arranging the data

[bb]

Hello everyone,

I have a tons of rows of data that looks like this:

City	Town	Name	Value
Boston	Cambridge	Database 1	Apple
Boston	Cambridge	Database 1 Type	Sql
Boston	Cambridge	Database 1 URL	high
Boston	Cambridge	Database 1 Server 1 FQDN	ft123yee
Boston	Cambridge	Database 2	Orange
Boston	Cambridge	Database 2 Type	Oracle
Boston	Cambridge	Database 2 URL	low
Boston	Cambridge	Database 2 Server 1 FQDN	ash67qweu
NY	Rochester	Database 1	Banana
NY	Rochester	Database 1 Type	Sql
NY	Rochester	Database 1 URL	medium
NY	Rochester	Database 1 Server 1 FQDN	123hasd98

I need to arrange the data in such a way that it lines perfectly based on City and Asset. So basically if it's the same city and sametown and want anything that has number after database, it should place it in different column as Database. Similarly, All the database type should be in the new column for database type and so on.

The output should look like this:

City	Town	Database	Database Type	site	FQDN
Boston	Cambridge	Apple	Sql	high	ft123yee
Boston	Cambridge	Orange	Oracle	low	ash67qweu
NY	Rochester	Banana	Sql	medium	123hasd98

Anyone knows how to do this? Thanks in advance!!

[JosephSerpis]

Hi @bb I mocked up a workflow that produces the output as described. Let me know what you think? 

[JosephSerpis]

Hi @bb  I mocked up a workflow let me know what you think? 

[Cal_A · Accepted solution]

Hi BB,

JS420's solution is great and uses the powerful RegEx tool.  If you find this tool a little opaque/scary you  can also breakdown the Name column using the formula tool.

The objective:

Before you can use Cross-tab you need to get to a point where you have a column of (future) headers and their corresponding values. Like the below picture:

i.e. something like this for Headers/Data fields

HEADERS	Values
Database	Apple
Type	Sql
URL	high
FQDN	ft123yee
Database	Orange
Type	Oracle
URL	low
FQDN	ash67qweu
Database	Banana
Type	Sql
URL	medium
FQDN	123hasd98

BUT You also need a way for the CrossTab tool to group (ie delineate) database1 and database 2.  Otherwise it will try to combine Apple+Orange into one row, because they are both Boston/Cambridge.

So, you have to split the Name column to give you two parts

Part1 = Database #

Part2 = New "Header" (e.g. Datebase Name, Type, URL, FQDN)

This can be done with a formula tool using 2 entries

1) Create new column for the header using this formula. Call the column "Header".

IIF(Length([Name]) = 10,"Database",Substring([Name],11))

2) Replace the existing "Name" column with this formula:

substring([Name],9,1)

You can now use CrossTab

• Grouping fields are City, Town, Name (Name now contains either 1 or 2)
• New Column Headers = The new column you created called "Headers"
• Values = "Value" field.

something like this

good luck

[estherb47]

Hi @bb 

And yet another approach here. If the Name column always contains the text that you shared, then you can solve for the column headers using a simple IF/THEN/ELSEIF and Contains function. The First function, Row, gives the number from the name column, which is needed to build the rows out with the Crosstab tool. Substring takes characters that don't come directly from the left or right side of the field. Just remember that it's zero-based, meaning the leftmost character is in the 0 position (which makes it different from Excel's MID function if you're used to using that)

I love using RegEx, but it can take longer in some cases, so I find that I use regular text functions instead, in cases where they will work.

A Crosstab tool rebuilds the table. @Cal_A provides a fantastic explanation as to the process.

Let me know if that helps.

Cheers!

Esther