Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtering with formulas?
I understand advanced filtering, but I've been trying to figure this
particular filter out for hours and it has me scratching my head (I think because I'm having trouble using formulas in my filters). What I'm trying to do is filter one column based on whether data in that column is equal to data in another column. For example: A B C D 1 10 12 14 10 2 10 11 15 15 3 12 12 14 12 4 11 15 10 11 Is there an easy way to filter where the data in column A equals the data in column D? So using the above examples the filter would return rows 1, 3, and 4? And a more complicated task, where data in column A equals the data in column D, and is less than data in column C (which should return rows 1 and 4). Any help is greatly appreciated! Thanks, -- gdmill |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtering with formulas?
I would put following formula in a different column:
=if(A1=D1) and then copy down then you can filter that column with true and you receive row 1,3,4 and for the second task: =if(and(a1=d1,a1<c1)) same thing as above hth Carlo On Jan 31, 8:36*am, gdmill wrote: I understand advanced filtering, but I've been trying to figure this particular filter out for hours and it has me scratching my head (I think because I'm having trouble using formulas in my filters). What I'm trying to do is filter one column based on whether data in that column is equal to data in another column. *For example: * * *A * * B * * C * *D 1 * 10 * 12 * 14 * 10 2 * 10 * 11 * 15 * 15 3 * 12 * 12 * 14 * 12 4 * 11 * 15 * 10 * 11 Is there an easy way to filter where the data in column A equals the data in column D? *So using the above examples the filter would return rows 1, 3, and 4? And a more complicated task, where data in column A equals the data in column D, and is less than data in column C (which should return rows 1 and 4). Any help is greatly appreciated! Thanks, -- gdmill |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtering with formulas?
Try this:
H = column header A B C D..............F..........G 1 2 H H H H 3 10 12 14 10 4 10 11 15 15 5 12 12 14 12 6 11 15 10 11 F1 should be left empty. Enter ths formula in F2: =A3=D3 G1 should be left empty. Enter this formula in G2: =AND(A3=D3,A3<C3) Now, for your filter criteria range use either F1:F2 or G1:G2 -- Biff Microsoft Excel MVP "gdmill" wrote in message ... I understand advanced filtering, but I've been trying to figure this particular filter out for hours and it has me scratching my head (I think because I'm having trouble using formulas in my filters). What I'm trying to do is filter one column based on whether data in that column is equal to data in another column. For example: A B C D 1 10 12 14 10 2 10 11 15 15 3 12 12 14 12 4 11 15 10 11 Is there an easy way to filter where the data in column A equals the data in column D? So using the above examples the filter would return rows 1, 3, and 4? And a more complicated task, where data in column A equals the data in column D, and is less than data in column C (which should return rows 1 and 4). Any help is greatly appreciated! Thanks, -- gdmill |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtering with formulas?
One observation, "where data in column A equals the data in column D, and is
less than data in column C (which should return rows 1 and 4)" is an incorrect statement. Using your condition, it should return rows 1 & 3, not rows 1 & 4. Anyway, on to the desired tasks. First, insert a blank row 1 and create column headers. To accomplish your first filter, enter the following in E2 (your first row of data), copy it down to the rest of the rows, then filter for TRUE in column E. =A2=D2 To accomplish your second filter, enter the following in F2 and copy it down to the rest of the rows, then filter for TRUE in column F. =AND(A2=D2,A2<C2) "gdmill" wrote: I understand advanced filtering, but I've been trying to figure this particular filter out for hours and it has me scratching my head (I think because I'm having trouble using formulas in my filters). What I'm trying to do is filter one column based on whether data in that column is equal to data in another column. For example: A B C D 1 10 12 14 10 2 10 11 15 15 3 12 12 14 12 4 11 15 10 11 Is there an easy way to filter where the data in column A equals the data in column D? So using the above examples the filter would return rows 1, 3, and 4? And a more complicated task, where data in column A equals the data in column D, and is less than data in column C (which should return rows 1 and 4). Any help is greatly appreciated! Thanks, -- gdmill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need filtering help | Excel Discussion (Misc queries) | |||
Filtering using Formulas | Excel Worksheet Functions | |||
Filtering | Excel Worksheet Functions | |||
filtering | Excel Discussion (Misc queries) | |||
trouble filtering a list. Why isn't column filtering? | Excel Worksheet Functions |