Transfroming unstrcuture data to structure data for automation

Hello all expert, this is real case that our company facing. ( Data is handled and some record i just modifed)

Here is the case, we got tons of htm file which company plan to do the automation to get the customer data.

But the data source is htm, the source data is based on the location , top , left, height and width and useful data is contains in <span></span> . Belows are some data example:

When you open in brower:

Open in note pad ++ :

What i cleaned data by alteryx: (data is modified )(higlited part is what we needed and the file will be attach)

my boss expected result :

I have tried few days still cant get the expected result i want without hard code, due to company want to do the automation.

What i did was only can find pattern in data source, you may see case UB1234 with same top value (1.8XXX), i was using filter to get the same top (hard code), And tried tile,and formula. But the last value of UB1234's top value (2.3XXX) also belong to same case.

Its the part im struggling i have no idea how to put same case data into same row and without hard code. Please help and give some recommdation. Big thanks.

Testing.xlsx

Transformation

Accepted answers

SPetrie

I may not be totally understanding the logic of how it needs to be split up, but I think I got it pretty close and may at least help you on the way.

Most of this is based off the last column starting with the UBcode and ending at either the next UB code or a Result: row.

mockup.yxmd

All comments

SPetrie

I may not be totally understanding the logic of how it needs to be split up, but I think I got it pretty close and may at least help you on the way.

Most of this is based off the last column starting with the UBcode and ending at either the next UB code or a Result: row.

mockup.yxmd

Lynch120

Hi ,

Thanks for your feedback.

But what if it's not based on UB. every number is special. how to do it ?

Coa your data flow is based on UB as a key,

SPetrie

@Lynch120

To answer your question, it would depend on the data. You have to look for and figure out what pattern or patterns are evident in your data and use it to break it up. If you cant find any, you have to make your own.

If I didn't use the UB code, I could have also used a combination of the 3rd and 4th columns. The combination of those two numbers also gives us a starting/stopping point between the items we need. The width also appears unique for the items we DONT want, so we could filter those items out as well.

I hope that helps with your question.

mockupv2.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335