index/lookup values based on one condition while skipping blanks and zeros.

This is a really bad layout and will make your life miserable!! It would be much easier if you used only one column of due to BIR (instead of 2) and used ONLY one block of data (columns B to H - assuming that old column G is deleted). If you do not do this, it will be a nightmare.

This sort of layout would be MUCH better!!

I must echo what Glenn has said. I was half way through a complicated array solution but then realised your data is not consistent

For instance the C10 result is found in AV5 the first of the "Due to BIR" pair of cells whereas the C9 result is in AP5 the second of the BIR pairs.



You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd create a single sheet database to capture your data

To Glenn's and Richard's posts, I add one more thing: Put the information in an Excel Table. Excel tables "know" how big they are so as you add or delete data from the table, it knows how many rows it has so formulas, pivot tables and charts that use the table as their source do NOT have to be changed as the data range changes. They will always access the exact number of rows needed.

The outline provided by Glenn is "normalized" data. Normalized data can be used by pivot tables readily to report the data in just about any format you can imagine.

Here is a wiki on tables to get you started: http://www.utteraccess.com/wiki/Category:Excel_Articles.

Originally Posted by Glenn Kennedy

This is a really bad layout and will make your life miserable!! It would be much easier if you used only one column of due to BIR (instead of 2) and used ONLY one block of data (columns B to H - assuming that old column G is deleted). If you do not do this, it will be a nightmare.

so sorry but it has to be like that as the actual sheet looks like that. There were columns that were deleted on that sheet to reduce the size of the sheet and is linked to other sheets with formulae. Anyway, i like how you changed the table into that but I cannot delete the other column of due to BIR because it has to be reported individually.
Later I wilo try to reconstruct this spreadsheet the way you presented. Thank you.

This sounds more like an issue of diplomacy than technical. Sometimes push-back is required.

There is a very good chance that the data already exists in the format recommended by Glenn, and that someone has taken the time to run a report against this data to put it in a more "human readable" format. This means that a program has to be written to convert it back into its original format. I wind up doing this about half the time. The other half of the time I can convince my data supplier to give me the data in a more usable format.

I suggest you send the workbook provided by Glenn to whoever is sending you this data and ask if they could provide the data in this format. Most report writers have the ability to dump data in this format into a CSV file.

The other possibility is that they are entering this data manually. If so, it appears to be a very error-prone process. In this case, it would be wise to give them an Excel form "front end" that saves the data in normalized format. If anybody really needs to see the information strung out horizontally as in the example, they can run a pivot table against it.

If they are doing manual data entry then the application would probably be better off in a database rather than a spreadsheet, but selling such a system to management is tough.

Good luck to you.

Sound words and good advice from dflak.

Hi,
I have changed the excel table nearly identical to the sample sheet presentation/construction. I know I may be asking for an impossible and a complicated one, but I still hope that I can come up with the desired results from you guys.

Thank you and happy new year!

Yes if it's possible it would be extremely complicated. For starters there are just too many merged cells. As has been suggested you should address the source provider and ask for a csv file. Don't accept no for an answer. WHy should yo be expectd to operate inefficiently when a change at source can save a lot of time and effort down the line.

I don't know how to get to your desired result. There are two BIG problems. The merged cell in column J and the show-stopping problem is the fact that there are STILL two target columns for "Due to BIR". Keeping them in separate rows, but together (the column P value for company X followed by the column Q value for company X) adds another level of complexity that I don't intend to tackle.

You have two choices. This, which gives you all the column P values and then all the column Q values, or a simpler formula (a little less than half the length) to give you the TOTAL of column P and Q per company. So, laneco would give one number - 149.17.

As you can see the formula is TRULY horrible. Thi sreturns the company name:

Formula:

Please Login or Register  to view this content.

Best solution is the INSIST on getting a flat CSV output and start with it.

Incidentally, a USEFUL sample sheet does not contain THOUSANDS of rows. I have set the formula only for as far as row 104. It is already a little slow. I have no idea how many rows you have.... but it may get very slow....