Combine Multiple Rows of Data Into One

[taran42]

I have been given some messy data to clean up, but I am having trouble getting it to where I want it. I would manually do it, but the data will come to me monthly, so I'll need to do it on a regular basis.

I need to get the Prop1, Address1 and Total from rows 2, 3 and 4 all on the same row. Then do the same with 5, 6, 7 and so on. I have tried transposing the data and filtering, then joining it back, but I can't seem to find the right combination. Does anyone know how I might be able to do this? 

[clmc9601]

Hi @taran42,

It depends on how you want to aggregate values in columns where both address and property have a value, for example. Here are solutions for a couple of different ways you might aggregate them. The data are strings here, but once you are working with numeric data, you'll also have the option to take the average, etc. 

Hope this helps!

 

[taran42]

@clmc9601 Thank you for your reply. In your example, you're creating a record ID from the number located in the property field. Is it possible to create a record ID to tie these rows together without doing that? In my example I gave, there was Prop1, Address1, Prop2, Address2, etc., but in my actual data there isn't those numbers to pull a record ID from, so I am having trouble getting the prop and address associated properly. Does that make sense?

[clmc9601 · Accepted solution]

@taran42, ah gotcha. There are (at least) two ways you can accomplish this: by referencing the names of each piece you want (from the example, "Prop", "Address", and "Total") or by creating record ID based on the number of parts you want per record. You'll probably have to customize this, but hopefully it works better for your data! The first multi-row can replace the formula tool if you choose to create by number of parts.

[taran42]

@clmc9601 Thank you very much for your help! I ended up taking the second RegEx you had and the middle Cross Tab (plus a few little extras) to get the solution I was looking for. I'm really hoping the data doesn't change formats, or else this might end up breaking.