Thread: Test and Compress Data

I'm trying to accomplish a good deal with one formula:

I have 4 data ranges, on different sheets in the same file-
A. 200x4 range (rows x columns)
B. 1x50 range
C. 1x25 range
D. 1x50 range

Range A contains a text label and 3 pieces of text data per row.
(Label, Data1, Data2, Data3) x 200 rows

Range B contains formulas that evaluate to text values or "". All of the text values in Range B match text Labels from the first column of Range A.
(Label1, Label2, Label3...)

Range C contains text values or blanks.
(Data1, Data2, Data3...)

The text values in Range C and in the last 3 columns of Range A are all drawn from the same larger pool of text Data items.

I want to put a formula in Range D, that will fill D with all the values in B whose corresponding 3 "Data" items from Range A are also found in Range C. If one of the 3 Data items in Range A are blank, it shouldn't require a blank value in C.
(Label1, Label3, Label6...)

And I want it to remove any blanks from the finished range in D.

The data and sizes of all the ranges are volatile enough that I don't want to name each of the 200 rows in Range A.

Can this be done with a formula in one cell? I'm hoping to avoid a VBA function, due to processing speed and the large number of calculations being performed.

TIA,

Bill

Bill

Can you please attach an example file of all this for us to review. Give some sample data and some example outputs you would expect to achieve.

rylo

Here's the sample file. All the data ranges are labelled to match my explanation above. I've filled in Range D manually with the results I'm after.

Thanks,

Bill

Bill

Have a look at the attached. I know I've got fixed ranges at the moment, but see if the approach will work.

If so, then can you advise where the range volatility will lie. Is it only in the length of the ranges B,C and D, or will range A also change size?

rylo

rylo,

Thanks for the help. Range A may increase in rows, but not columns. The other ranges will eventually be stable, once I decide how many of excel's precious rows to devote to each range.

Your solution is a big help, and gives me insight into some formulas I haven't used before. Is there a way to use the SMALL function to compress text arrays? I see you use numbers in your implementation of SMALL.

I'm trying to minimize the number of cells used for this solution. Although I show Range D as 1 row x 50 columns, that is in reality only for one entry of hundreds. Range D, in my usage, is actually more like 500 x 200, and I'm just looking at one fifty column slice of one entry. There are basically 500 unique entries, each entry has about 200 data items.

With that in mind, I'm trying to avoid putting any data or formulas in intermediate cells, as you have done. I've attached my current attempt, which uses only the cells in Range D. (I actually used a similar range labelled "My Current Attempt", to leave Range D as I originally posted it.)

The shortcoming of my approach is that I can't figure out how to eliminate the blanks - again without using any intermediate cells. If I use intermediate cells, I either have to give up another 50 columns per entry, or I have to use 2 rows per entry, thus preventing me from using the fill function smoothly.

I realize I may just have to add another tab to my spreadsheet, and use that tab for intermediate calculations. I'll do that if I need to, but would like to avoid it, as my actual spreadsheet already has 12 tabs, all full of final data, and doesn't yet have any "scratch paper" tabs.

Thanks for all your help on this,

Bill

Bill

Would it be possible to avoid the mix of horizontal and vertical arrays, and have them all vertical.

In the attachment, I've made them all vertical, and reduced the number of "helper" columns to 2. I've also made the formulas apply to whole columns where I can, and also create a couple of dynamic named ranges so they will react to the data size.

See if this gives you some more ideas.

If that fails, then I think the only option available to you is a code solution. Again it would be much easier to work if you could have vertical arrays.

rylo