Horizontal data Formula

Hello Everyone,

I would to come with a formula that i have a Cell A1 that have dates horizontally. Some dates don't have any amount and some have it, but I would like to see it Vertically referring Cell A1 with dates and amount in vertical way. Also, I dont want any "0" data...

See attached for reference.

Thanks,
MO

Why do you show the dates going backwards for CTR# 11950000, but going forwards for CTR# 11300000 ?

What does the 01.19 signify at the end of the dates in column D ?

Pete

A couple of questions in addition to Pete's:

1) What does the minus signs in front of the CTR# 11950000 dates in col-D mean?

2) Is this to be a "one-off" rearrangement of the worksheet or will it need to be done multiple times / regularly?

1) That's just a dash to show the date that I type it manually.

2) I will get a file like that every month from our vendor.

Thanks,
Moustapha

Originally Posted by Pete_UK

Why do you show the dates going backwards for CTR# 11950000, but going forwards for CTR# 11300000 ?

What does the 01.19 signify at the end of the dates in column D ?

Pete

Dates I type it manually. Hope that helps.

01.19 its the current month.

And how much data are you likely to have in your real file?

Pete

Around 2630 rows of data

Here is a VBA solution. It assumes the following:
The name of the source data (from vendor) worksheet is "Source"
An empty worksheet named "Dest" exists. This is where the re-arranged data will be placed.

I have chosen to provide a real date in column-D. It is derived from the months provided in source data row-1, but as row-1 is just text there is no year data available. For now I have assumed that the starting year in cell B1 is 2018. This can be changed easily if necessary by changing the startYear constant near the top of the VBA.

To test this macro:
1) Add/change the data on the source worksheet as desired.
2) Clear the "Dest" worksheet
3) Run the "reorg_data" macro.
4) Verify that the reorganized data on the "Dest" worksheet is as desired.

Here is the VBA:

Please Login or Register  to view this content.

See also the attached workbook

it works perfect. Can you teach me please step by step how you started with this macro?

I'm not sure what you VBA knowledge level is. If you are just starting out then I would recommend that you find a few of the many online tutorials that interest you and study them. I also consider myself a rank amateur at VBA - there are many experts on this forum who would have made a better job of this - so don't necessarily take my code as a great model!

That said, here's a summary.

The heart of this VBA sub is the two nested for loops.

The outer loop iterates around each row (except the header row) of the source worksheet and saves the CTR# of the row so that it can be output to potentially multiple rows of the destination worksheet.

The inner loop iterates around each cell in the current source row. The "IF" statement ignores "amount" cells that are set to zero per your requirements.

If the "amount" cell is not zero then a new row is written to the destination worksheet consisting of the CTR# and the "amount". The "round" function computes a rounded version of "amount" per your requirement. Computing the date, in particular the right year, is less than straightforward because the month in row-1 of the source data is just text, not a date and so there's a bit of math using the VBA month, Int and DateSerial functions in order to derive the date that I have written to col-D of the destination worksheet.

That's it in summary. If you have specific questions I will be glad to try to answer.