cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lcuellar75 Frequent Visitor
Frequent Visitor

Need help extracting data from stacked column with no pattern

Hello,

I am having a very hard time trying to extract data from a Tabula export that stacks my data in one column. I have seen several methods using a modulo column, but they all require the data to be stacked in a pattern. My data is stacked like this:

Invoice
A11111
SKU
55555
55556
55557
Price
2.25
3.25
4.25
Invoice
A22222
SKU
66669
66668
66667
66666
66665
Price
1.25
1.75
3.25
6.25
6.25

Where the prices correspond the product above them. I want to get a table with an Invoice, SKU and a Price column. Product 55555 has a price of 2.25 and product 66669 has a price of 1.25, etc Please let me know if you have a trick for this!
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Need help extracting data from stacked column with no pattern

Hi,

 

You may refer my solution workbook here.

 

Hope this helps.

 

Untitled.png

5 REPLIES 5
Super User
Super User

Re: Need help extracting data from stacked column with no pattern

Hi,

 

Does the invoice always have "A" at the beginning?  Is the SKU always a length of 5?  Share patterns, if any, for the invoice number and SKU.

Lcuellar75 Frequent Visitor
Frequent Visitor

Re: Need help extracting data from stacked column with no pattern

Thanks for responding! The Invoice is always 6 characters: a letter followed by 5 numbers (V70587, A30785). The SKU is always 8 digits, no letters. Here’s some of the actual data:

INVOICE:V68758
SKU
63050150
64050008
PRICE/
10.99
17.99
INVOICE:V70518
SKU
25160855
33901225
33601100
25160020
25160551
PRICE/
11.99
2.79
3.99
7.99
6.59

Let me know if you have more questions.
Highlighted
Super User
Super User

Re: Need help extracting data from stacked column with no pattern

Hi,

 

You may refer my solution workbook here.

 

Hope this helps.

 

Untitled.png

Lcuellar75 Frequent Visitor
Frequent Visitor

Re: Need help extracting data from stacked column with no pattern

Thank you so much, this is very helpful!

Super User
Super User

Re: Need help extracting data from stacked column with no pattern

Hi,

 

If my solution helped, please "Accept it as a solution" and give a Kudos.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 46 members 1,118 guests
Please welcome our newest community members: