PDA

View Full Version : List Ranges with Missing Data in Column



Ethan
07-08-2011, 02:54 AM
Hello all,

Is it possible to list ranges in A:G with missing values in collumn D.
See attachment

Aussiebear
07-08-2011, 03:23 AM
Hello all,

Is it possible to list ranges in A:G with missing values in collumn D.
See attachment

1. If you mean can you name a range even if data is missing from some of the cells, then most certainly, Or,

2. Are you trying to find those rows with the missing data in column D?

Ethan
07-08-2011, 04:02 AM
1. If you mean can you name a range even if data is missing from some of the cells, then most certainly, Or,

2. Are you trying to find those rows with the missing data in column D?


The second :yes

Zack Barresse
07-09-2011, 10:41 AM
You could do this with formulas if you would like. Utilizing your workbook, place the following values in K1:O1 (or wherever you like, but that is the range I used):

NAME, COMPANY, IN, PASS, Eetc

List all of your criteria underneath them, so starting in K2 you would have these comma-separated values:

John, Douane, 0:45
Marie, Flexcargo, 11:37

Then in only N2 type in this formula:

=INDEX($A$2:$G7,MATCH(1,($A$2:$A7=$K2)*($B$2:$B7=$L2)*($C$2:$C7=$M2),0),COL UMN(F1))

Only when you confrm it, instead of using Enter as usual, use Ctrl + Shift + Enter, which enters it as an array formula. It's important that this is the only cell selected at the time, otherwise you will confirm a range of cells as an array, and that's a PITA. Once that formula has been put in correctly, Excel will add curly brackets surrounding the formula when you look at it in the formula bar. Note that this is good and normal, and you should not add these manually.

Next, select N2 again, hover your cursor over the bottom right corner, drag one column to the right, then drag it down as needed.

Edit: Note that this will not expand with your original data table. If you wish to keep this dynamic, and I'm assuming you're using Excel 2007 or above, you could utilize tables. For this formula example I inserted a Table as your original data range and called it DataTable (which you can do from the Table Tools | Design ribbon tab). The formula would then become:

=INDEX(DataTable,MATCH(1,(DataTable[NAME]=$K2)*(DataTable[COMPANY]=$L2)*(DataTable[IN]=$M2),0),COLUMN(F1))

And thus it would be dynamic.

HTH