1

I am creating a report from Cognos which is generating the result below in Excel format:

The problem with the code is everything I have hardcoded the values based on line numbers and which I want to make dynamic, so that if I have to generate a report for the previous 2 weeks, then I won't have to change my macro code every time.

Currently I am showing the value for name = "PO" and id = 28. But there will be other name and id also. For every name and id there will be 4 column values as for RR, DD, EE and FF. I have to group them based on name and id and create new sheet which will have results like below:

Andrew
  • 3,632
  • 24
  • 64
  • 113
  • You need to seach for each entity and establish it's row, column etc. It's possible, I've done it. You need to define your own structure to search though. –  Nov 15 '17 at 16:17
  • 1
    Also, it's best to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/), which also will help cut down the code length. Also, it looks like mainly you just need to get your headers set up, and you could use `Index/Match()`, no? (Perhaps with multiple criteria) – BruceWayne Nov 15 '17 at 16:25
  • yes but i am quiet new to vba coding ang getting idea about the same – Andrew Nov 15 '17 at 16:26
  • You can do all this without VBA. Have a look at Get & Transform on the Data tab – Sam Nov 15 '17 at 16:34
  • ... with all respect Andrew! –  Nov 15 '17 at 16:35
  • @Sam without VBA how can we dont ?thanks. – Andrew Nov 15 '17 at 16:42
  • I repeat: You cannot do this unless you are fluent in VBA. Now you are way out your league. Sorry –  Nov 15 '17 at 16:53
  • @peakpeak if you can do this please help me – Andrew Nov 15 '17 at 16:54
  • When I started programming in 1980 there was noone to ask. Today we have Google. Learn from it! –  Nov 15 '17 at 16:58

1 Answers1

1

Andrew: What version of Excel do you have? If you have Excel 2013 or later, you can use the new functionality under the 'Get and Transform' tab to do this really easily. It was previously a separate add-in called PowerQuery, and is now bulk standard in Excel.

There's many excellent walkthroughs on the internet showing this exact type of activity. Google "PowerQuery" and put a date filter on your results to show just the last year, as the functionality is constantly being updated. Add "Ken Puls" to your search term, and see what bubbles to the top. Then replace Ken "Mike Girvin" or "ExcelIsFun" and you should see like a zillion excellent tutorials on PowerQuery. Try "ExcelCampus" too, as Jon has great tutorials, as does "Chandoo". Also replace PowerQuery with "Get and Transform" and repeat those same searches.

PowerQuery is simple to learn, and lets you do stuff just by "muddling through" that would otherwise take a year or more to learn if you wanted to pick up VBA.

It acts like a user-friendly macro recorder, only unlike the macro recorder it spits out reusable code that doesn't have hard coded references in it.

jeffreyweir
  • 4,668
  • 1
  • 16
  • 27
  • thanks for your information i am just going through get and transform functionality. Just wanted to know for my example do you suggest any specific tutorial which i look into. Otherwiese i have to spend lot of time by learning through all the functionality of get and transform ? – Andrew Nov 15 '17 at 19:49
  • It won't take you too long to find something similar, using those names I gave you as google filters. And then you'll quickly see just how easy it is to muddle through using this tool. I use it all the time to do stuff that I have no idea how to do, and I've found time and time again that the tool is very forgiving to lack of knowledge. – jeffreyweir Nov 15 '17 at 19:57