Combine Multiple Rows of Data Into One

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?

sample messy file.yxmd

Preparation

Accepted answers

clmc9601

@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.

Screen Shot 2021-01-14 at 7.51.58 AM.png

sample messy file.yxmd

All comments

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!

Screen Shot 2021-01-13 at 2.47.46 PM.png

sample messy file.yxmd

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?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15874

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703