Selecting non-contiguous data through formula

[RichP]

Hi again,
I've got stuck while working on an excel-based database, and I need some help figuring out the next step. Ideally I'd talk this through with someone over a coffee to find some inspiration, but lacking people with sufficient excel knowledge to ask, I turn to the good people this board. I hope you might be able to help, please?

To try and explain the situation: I have a workbook with a series of sheets (approx 100), each one named after a Region, and on each sheet I have data arranged under the following headings starting in column A:
Date, Company, Item1, Volume1, Price2, Item2, Volume1, Price2, Item3, Volume3, Price3, etc. to allow for a total of 25 items; worth noting that not every region uses 25 items, but some do and others might in the future, therefore each sheet allows for this possibility. I have allowed for up to 200 entries in total per Region

Each time that data is entered into these sheets (through a user form), it goes into the next available empty row, and starts with Date and Company, and depending on what ItemNumber the information relates to, and then goes into the corresponding columns. There will always be data in columns A and B of each row, but then C, D, E may be blank in one row while F, G,H will have data, etc.; there is no regular pattern to which group of columns contains data.

In a separate sheet, I am trying to make a report where I can view all Date, Company, Volume, Price information gathered for the Region and Item that I select from two dropdowns - so RegionA, Item2, or RegionEF, Item2, and so on. At the moment I am happy only looking at one Region and Item at a time.

I have got as far as displaying the data quite happily and neatly in my "report" table using an Index-Match system, but I started by placing the formula, with a different row component, into each row of the report - so row 1 of the report shows the data in row 1 of the Region table, and so on. Where the problem arises is if there is data in rows 3, 4, 5, but not in 6, 7, 8, and then again in 9, 10. Because the formula in each row is predicated on a specific row number, I get lots of blanks, and I would like to have the data all nice and compact, without blank rows.

Is there a way of doing this with formulas, so that I don't have to run VB? The easiest way, with VB would probably be to get all the information across from the relevant sheet into the report, then run a filter so you only see the information for the corresponding Region and Item. Not very neat, but before I go down that route, I'd like to try using just formulas. Any idea on how to make the formula skip the blanks?

Thanks,
Richard

 

[Rick Rothstein]

I would guess one of our formula wizards (of which I am not one) might be able to do that for you (but my gut tells me such a formula would not be "pretty"); however, I am wondering why you want to go the formula route for this? I mean, you are already using VBA code for your UserForm, so why the aversion to using it here as well?

 

[RichP]

Thanks Rick, I guess in part I was trying to keep the VBA confined to the userform, and make the reports as apparently (to the user) simple as possible. The other part of me, if I'm being honest, wants the challenge. It has been about 10+ years since I had to use Excel in anger, and while macros are fun, I'd like to see what I am trying to achieve done using formulas. And whether, with or without help, I can manage...

Coming back to my current predicament, I think there might be a way to do get where I want using Index, Match and Small functions - Small should be able to tell me what rows contain the relevant information, and then Index Match might be able to take care of the rest. I think... If I get nowhere I'll revert to macros, don't worry!!
Thanks again,
RP

 

[XOR LX]

Hi.

Not quite sure I understand your request, but is the following post of mine of any use?

Collating from multiple sheets based on conditions « EXCELXOR

Regards

 

[SpillerBD]

except for that being a lot of sheets, did you consider Pivot Table with "Multiple Consolidation ranges" ?

 

[RichP]

Spiller - I didn't consider it, based on it being so many sheets, and that I am awful at dealing with Pivot tables! When last I used excel I could rarely get them to work consistently, and I guess it has stuck with me to avoid them at all costs. I will look into them again, however.
XOR - wow... just wow! Exactly the sort of thing I was looking for. While what you have linked to isn't exactly what I am after as for now I'll be dealing with one page at a time, it will be a basis for a solution here, and I will probably do something with it in future.
For now, I've got as far as working out a way of getting the row numbers where the entries are in the selected group of columns, irrespective of their being blanks in between (combination of small and row functions), so I'll push on with that, while I digest XOR's solutions.
Thanks again,
RP

 

[XOR LX]

You're welcome! But wait, are you saying that you're seeking to obtain data not from various sheets, but in fact from various columns within a single sheet?

Can you give a small, mocked-up example?

Cheers

 

[RichP]

XOR -
the data is held in approx 100 sheets. All have the same format. With the "report" I'm trying to create at the moment, only data from single sheet will be used at any one time. The the info from your article on collating from multiple sheets, gives me an idea for how I may well try to work across all 100 or so sheets at once, but for a different purpose than this particular report. Your approach of not using VBA is what I was looking for. I've started to go down the right route, I think, using Index, Match, Row and Small (the last two to help provide relative positions within columns, then index/match, to return the data).


In terms of the layout of the sheets, for the initial purpose, the layout is:

Date of Info|Selling Company|Customer|Item1 |Volume1|Price1 |Item2|Volume2|Price2|Item3 |Volume3|Price3 => through Item25
09/07/14 |Company1______|Cust1___|Prod1 |250tons |$650/t |_____|_______|_____|Prod3 |200tons |$700/t =>
09/07/14 |Company2______|Cust1___|Prod1 |250tons |$655/t |Prod2 |800tons|$600/t|Prod3 |200tons |$695/t =>
09/02/15 |Company1______|Cust1___|Prod1 |400tons |$595/t |Prod2 |700tons|$610/t|Prod3 |180tons |$750/t =>
09/02/15 |Company3______|________|_____|_______|______|Prod2 |700tons|$600/t|_____|_______| =>
07/05/15 |Company1______|Cust1____|Prod1 |400tons |$595/t |Prod2 |700tons|$610/t|Prod3 |180tons |$750/t =>
07/05/15 |Company2______|________|_____|_______|______|Prod2 |700tons|$600/t|_____|_______| =>

And so on. On each sheet there could be up to 15 different companies, up to 15 different customers, and for any given date, there could be between 0 and 25 items worth of data. Each line represents one data entry event, and one particular customer, but could have data for more than one entry. In effect I'm tracking market data, each line represents and offer made to a customer for one or more items. There is only ever a max of 25 Items, and in each location the items are different, but keep a similar hierarchy, so there is always an Item 1, which is the least valuable through to Item 25 which is the most valuable.
The report part of it is designed so that I can select the Region I want to look at, then pick an Item type (1-25), and see all the prices and volumes that have been recorded, who offered them and to whom. This is what I was trying to describe as non-contiguous data: for any given row, there may not be data for every item, but they will all have something in the date, selling company and customer fields. Where I am/was getting stuck is making a neat table in the report to show all the data available for Item1 without gaps.
Hope this helps, but I suspect it's still clear as mud.

 

[XOR LX]

Actually, I think I understand your requirement, and, if as you say you're only ever dealing with one sheet at a time, then an INDEX/SMALL combination will be the standard set-up to return the required records which match certain criteria.

Let me know if you need any further help refining your solution.

Regards

 

[RichP]

Thanks. Around 4am this morning it occurred to me that if I could use Index/Small to correlate two values, I might be able to consolidate everything onto one sheet. This leads to a rather more general question: is it better (from a management, stability, etc. point of view) to have one table that is potentially 600 columns by 7000 rows and contains all the data points, or to keep going with my idea of splitting the table onto various sheets, by region?