Excel for dummies (me)

[Zico]

It's been a very long time since I have used Excel and I've forgotten a lot of the formulas that should make things easier.

I'm trying to get the totals for several organisations, that are on multiple worksheets, with each worksheet containing a mix of organisations but with the same layout.

For example, all worksheets will have column A as Organisation, but the list will be in a different order.
So Org A, B, C in one worksheet but maybe Org A, C, D and E in the next.

To further blow my brain, I also want to try and sum the value of other columns based on the organisation found in column A.

I've been stuck between COUNTIFS, SUMPRODUCT and SUMIFS but just keep getting muddled.

 

[gpuerrilla]

Use Power Query. What version of Excel you using?

 

[Zico]

Running Excel 2019.

 

[gpuerrilla]

OK can you upload an image of the sheet layout or link it so I can take a look?

 

[gpuerrilla]

Use the Ribbon. Data > Get Data > From Other Sources > Blank Query.

Too long to explain the rest as so many professionals explain it very well so take a look at this one and you will see its really easy just follow the instructions. You will find its really decent and forget the formulas and cell faffing once you digest the tools are great.

 

[Zico]

Evening @Th0nt , sorry I completely forgot I had posted here. I was so busy with trying to finish off work and other things last week I just forgot about this post.

I should have explained a little better that I'm getting 5 or 6 different Excel CVS outputs from a main database and was trying to merge them all (something I now realise can't quite be with Power Query).

As much as I would like the help in getting to grips with things, I feel like it's cheating to just get someone to point out what to do. I'm going to make some time to go over things in the next few weeks as I've got a friend who's also doing something similar with the data using Power Bi (something that I'm going to need to get to grips with as well).

If you have any good guides or links to any that cover Power Query then that would be great.

 

[gpuerrilla]

Evening @Th0nt , sorry I completely forgot I had posted here. I was so busy with trying to finish off work and other things last week I just forgot about this post.

I should have explained a little better that I'm getting 5 or 6 different Excel CVS outputs from a main database and was trying to merge them all (something I now realise can't quite be with Power Query).

As much as I would like the help in getting to grips with things, I feel like it's cheating to just get someone to point out what to do. I'm going to make some time to go over things in the next few weeks as I've got a friend who's also doing something similar with the data using Power Bi (something that I'm going to need to get to grips with as well).

If you have any good guides or links to any that cover Power Query then that would be great.

This is absolutely perfect for Power Query! lol

Download all your csv to one folder. Use Get Data > From File > From Folder.

Search youtube for power query from folder.

Ed- btw Power Query is based off BI. Its the poor mans version free with Excel if you like. So both very similar and worth using.

Once you have your query grabbing the csv data you can merge them.