VBA // How to convert the data from Column to Row as per conditions

[riya]

Hi All, I have the input data as per the attached excel file and want the output from VBA. Could you please help me out. Please remember sometime we get "Amount4" in "H" Column. Thank you

INPUT
City Code Country Amount 1 Amount 2 Amount 3 Los Angles LA US 40 50 90 Oakland OA US 21 78 8 Savannah SA US 87
Toronto TO CA 90 0

OUTPUT
Los Angles LA US Amount 1 40
Oakland OA US Amount 1 21
Savannah SA US Amount 1
Toronto TO CA Amount 1 90
Los Angles LA US Amount 2 50
Oakland OA US Amount 2 78
Savannah SA US Amount 2 87
Toronto TO CA Amount 2 0
Los Angles LA US Amount 3 90
Oakland OA US Amount 3 8
Savannah SA US Amount 3
Toronto TO CA Amount 3

 

[GraH - Guido]

Hello riya

Just one question, must it be VBA, or is another automation also fine?
Power Query may be ideal for this. I have applied 5 steps (Only showing the first 4 in the screenshot with the user interface) to transform your data into the table you like. It is a transformation that is called unpivot. One of the many actions PQ can undertake. It took me not even 5 minutes to do this.


You can use PQ in Excel versions 2010-2013-2016 (for windows). If this is not applicable for you, then some-one will provide the VBA, I'm sure. Else, happy PQ discovery. You'd be amazed what it all can do...

If you some more help, please let me know. Happy to elaborate a bit more.

Greets
G.

 

[vletm]

riya
I hope that You would like colors and all columns too as Your sample output.
Press [ Do It ] to get it!

 

[riya]

Hello riya

Hello [URL='https://chandoo.org/forum/members/riya.46887/']riya

Just one question, must it be VBA, or is another automation also fine?
Power Query may be ideal for this. I have applied 5 steps (Only showing the first 4 in the screenshot with the user interface) to transform your data into the table you like. It is a transformation that is called unpivot. One of the many actions PQ can undertake. It took me not even 5 minutes to do this.
View attachment 48656

You can use PQ in Excel versions 2010-2013-2016 (for windows). If this is not applicable for you, then some-one will provide the VBA, I'm sure. Else, happy PQ discovery. You'd be amazed what it all can do...

If you some more help, please let me know. Happy to elaborate a bit more.

Greets
G.

Hi Thank you,, but i have to done only IN VBA Actually this is the part of my Macro. If possible please please provide me the VBA code

[/URL]
Just one question, must it be VBA, or is another automation also fine?
Power Query may be ideal for this. I have applied 5 steps (Only showing the first 4 in the screenshot with the user interface) to transform your data into the table you like. It is a transformation that is called unpivot. One of the many actions PQ can undertake. It took me not even 5 minutes to do this.
View attachment 48656

You can use PQ in Excel versions 2010-2013-2016 (for windows). If this is not applicable for you, then some-one will provide the VBA, I'm sure. Else, happy PQ discovery. You'd be amazed what it all can do...

If you some more help, please let me know. Happy to elaborate a bit more.

Greets
G.

 

[riya]

riya
I hope that You would like colors and all columns too as Your sample output.
Press [ Do It ] to get it!

Waoooo ... You made my Day.. Thanks a lot... you are Awesome.

 

[riya]

Hello riya

Just one question, must it be VBA, or is another automation also fine?
Power Query may be ideal for this. I have applied 5 steps (Only showing the first 4 in the screenshot with the user interface) to transform your data into the table you like. It is a transformation that is called unpivot. One of the many actions PQ can undertake. It took me not even 5 minutes to do this.
View attachment 48656

You can use PQ in Excel versions 2010-2013-2016 (for windows). If this is not applicable for you, then some-one will provide the VBA, I'm sure. Else, happy PQ discovery. You'd be amazed what it all can do...

If you some more help, please let me know. Happy to elaborate a bit more.

Greets
G.

Hello... Thank you.. But i have to work only in VBA

 

[GraH - Guido]

riya
I hope that You would like colors and all columns too as Your sample output.
Press [ Do It ] to get it!

Once again, nicely done.

 

[riya]

Hello... Thank you.. But i have to work only in VBA

Hi Vletm, Extremely sorry i am not able to implement in my main file. Please see the attached file. Please ignore color which i filled only for understanding. Also note in the Input sheet we might get RATE 4 and RATE 5 in "Q" and "R" column in future. So we have to run Loop till "R" column. If possible please look the file.

 

[riya]

riya
I hope that You would like colors and all columns too as Your sample output.
Press [ Do It ] to get it!

Hi Vletm, Extremely sorry i am not able to implement in my main file. Please see the attached file. Please ignore color which i filled only for understanding. Also note in the Input sheet we might get RATE 4 and RATE 5 in "Q" and "R" column in future. So we have to run Loop till "R" column. If possible please look the file.

 

[GraH - Guido]

Hello... Thank you.. But i have to work only in VBA

Care to explain why you have to work in vba? Just for my information. This insight may be useful for me later.

 

[riya]

Care to explain why you have to work in vba? Just for my information. This insight may be useful for me later.

Actually the "INPUT Data" also comes from some VBA query thats why i have to use only VBA to get the "OUTPUT" and later on i have also assigned a macro in Output file so it will proceed further. I had done all. but stuck in the Output file. if Possible please give the OUTPUT as per the file. Much thanks

 

[vletm]

riya
Hint ... It would be much easier for You
if Your sample file would be as near as Your normal file ...
like layouts, colors, borders...
>> here You are... press [ Do It ] and see...

for GraH - Guido
is it true that, it (PQ) cannot handle colors nor borders?

 

[riya]

riya
Hint ... It would be much easier for You
if Your sample file would be as near as Your normal file ...
like layouts, colors, borders...
>> here You are... press [ Do It ] and see...

for GraH - Guido
is it true that, it (PQ) cannot handle colors nor borders?

Thank you Vletm.. Could you please let me what exactly below Numbers are working ?
a = 13
r = 3
IC = 65535

If IC = 65535 Then
IC = 5296274

 

[vletm]

riya
a = column which those 'RATE..' starts
r = 1st row from 'output'
IC = those are cells colors to output as You needed;
65535 is 'yellow' and 5296274 is hmmm ...'~green'.

 

[GraH - Guido]

Actually the "INPUT Data" also comes from some VBA query thats why i have to use only VBA to get the "OUTPUT" and later on i have also assigned a macro in Output file so it will proceed further. I had done all. but stuck in the Output file. if Possible please give the OUTPUT as per the file. Much thanks

On the other hand, input data could also be loaded via PQ... But, if you have done all the effort, you want a full solution in VBA; that's understandable.
For future perspective and FYI, you might want to check out the features of PQ when you have time. It can do/automate a lot, and basically it is easier to use then to write VBA. Look at the example: 4 applied steps versus a code of 37 lines.
To put it in perspective: I started using VBA after a training of 3 days and it took me about 3 weeks to complete my first VBA project. Without any training or example, I made an award winning (much like the trophies here, don't get too carried away) solution at work based on PQ, which was my first ever PQ. It took me one evening to make it. I just explored the UI and got started. Now I'm not self-promoting (is against the house rules), I am promoting PQ. It is just a smart tool. Good luck with the VBA provided by Vletm. And thank you for the quick reply.

 

[GraH - Guido]

to answer this:
for GraH - Guido
is it true that, it (PQ) cannot handle colors nor borders?
_____________
PQ can read data, transform it and loads the result in a table. That is it. So indeed, you cannot read/apply borders/colours like with VBA.

 

[riya]

riya
a = column which those 'RATE..' starts
r = 1st row from 'output'
IC = those are cells colors to output as You needed;
65535 is 'yellow' and 5296274 is hmmm ...'~green'.

Hi Vletm. Well Noted.. will it work if we want to do particular data/filter out in Input file. I have type a code to take "BAS" data from Input file. but it takes all data

 

[vletm]

riya
... someone just remember to mention this minor feature ...
I did some modifications to code and
now it would take care filtering too.
I didn't change the whole idea ... just add that feature.
Something else?