Create unique list of values from two ranges with criteria

I have two lists of values in separate columns with condition in adjacent cells, all on the same sheet

List 1 consists of numbers from 1 to 12, column to the right is either blank or Free
List 2 consists of numbers from 13 to 24, column to the right is either blank of Free

List 1 starts in A2, B2 is either blank or Free
List 2 starts in D2, E2 is either blank or Free

In column G, from G2 i want it to list all values from List 1 and List 2 where their status is Free

attached file has the complete example

thanks in advance of any assistance

Hi k64

I like what you did in A27 : D27
shame it didn't cope with multiple ranges in the same formula, but as values 1-12 and 13-24 are fixed, I can just use a helper column to replicate what you did

when i was researching this type of problem, I could find one close enough to my scenario, they were either too simple or too complex, no goldielocks

many thanks

32

Tony Tony Tony

that is ace ace ace

all in one solution, excellent

@k64 - thanks for your solution, but going with Tony's, hopefully we can both learn something by seeing how Tony worked it out
so high fives all round

@tony - use of column I for row number, guess it won't matter if this is changed to any other column ?

thanks again all, marked as solved and rep awarded

32

@k64 - "You must spread some Reputation around before giving it to k64 again."

sorry couldn't add rep

I made an error made by many other users of the forum

I thought by making the example as basic as possible I'd be able to make modifications later to suit exact requirements

when i added headers and such, I couldn't make the necessary adjustments for it to work

List 1 is fine, it just doesn't give any results from List 2

List 1 now starts in column B
List 2 now starts in column F

condition for List 1 is in column D
condition for List 2 is in column H

row now starts on 7 rather than 2 as in the initial example

nm

Please Login or Register  to view this content.

i had a couple of problems, i was starting the formula on row 6 rather than 7 and also checking for Complete rather than Free

all's good again

is it possible to do something like

Please Login or Register  to view this content.

to test if it's blank or is Free

going back to the original layout

the {"Free",""}
works for List 1

but it misses out rows 2 to 6 for List 2

Please Login or Register  to view this content.

excellent

thanks again

32