VBA / Macro / Formula help needed for extracting data and Transpose into another sheet

Folks,

Please help me to extract data from one sheet to another sheet then transpose automatically.

I have attached the work book of what I'm trying to accomplish.


Thanks in advance for your expertise!

Hi joshi,

Here is a Pivot Table (no vba needed) that comes close to what you want. See if it works for you.

Hi Marvin,

Thanks for your reply and advice. But unfortunately that will not suit for me, since I have to link the all data into another sheet for a conditional formatted Gantt chart and some reports.

Hence can you please help me to fix the data as per my original data structure?

Thanks,
Joshi

Hi Joshi,

The data on Sheet1 is much better to work with. Trying to convert it to what you want on sheet 2 is a step backwards. What kind of CF Gantt chart do you want. I believe it you can make your charts from Sheet1 data so you don't need to convert to sheet 2....

I get all kinds of question on how to convert data from your sheet2 form to sheet1 for, which is a much better TABLE format/style.

I think you should work your data from sheet1 NOT from sheet2. I just can't help you make your problem harder and data worse...

Sorry...

Hi Marvin,
Thanks for your advice. Please someone help me to fix this. Please

Hi Joshi

I'll look at it...have some ideas...only guarantee, you'll get your money back if it doesn't work. Be back to you.

Hi Joshi

It appears your Data is grouped by 7...is this ALWAYS the case?

One Data Element, Gate.Tr-1&2, has 2 groups of 7 for a total of 14. Will there EVER be MORE than 2 groups of 7 for ANY Data Element?

Hi John,

Yes the Data is gouped by 7 always (G-1, G-2, CER, G-3, G-4, G-5 and G-6). And the group of 7 will be more than 2 groups and less than 11 for each Data Element.

Thanks,

Joshi

Hi Joshi

Try this Code in the attached

Please Login or Register  to view this content.

Hi John,
Awesome..!!!!! Your code is working excellent. Thank you so much.
For a contingency, if the 7 group will come as a single for any data element (e.g. if Gate.Tr-1&2 has only G-1 then the G-1 start and finish dates should show in the table). Is this possible with minor code changes? If no, please ignore it since your existing code is more than enough for me.

Thank you very much once again for your time and help.

Hi Joshi

No, not a minor change...major change.

I've not tested it but the logic of the Code will probably skip any that don't have a Group of 7.

Hi John,
Sorry for bothering you, today I have noticed an error in your code. Please see the attachment and kindly advise.

Thanks

Hi Joshi

Since I have to revisit the Code tell me about this

For a contingency, if the 7 group will come as a single for any data element (e.g. if Gate.Tr-1&2 has only G-1 then the G-1 start and finish dates should show in the table

Let's assume a group has only a Single Element (or two, or three)...will that group ALWAYS contain a G-1 record? I'm looking for a Break Point...will a Group ALWAYS start with a G-1 record?

Yes, a group will always start with G-1 record.

Hi Joshi

These two issues have been addressed

today I have noticed an error in your code

and

For a contingency, if the 7 group will come as a single for any data element (e.g. if Gate.Tr-1&2 has only G-1 then the G-1 start and finish dates should show in the table).

Let me know of issues.

Please Login or Register  to view this content.

Hi John,

Thank you so much your code is working perfect..!!!. The Thread is SOLVED now.

Thank you very much once again for your time and help.

You're welcome...glad I could help. Thanks for the Rep.