Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing figures across 2 columns.
Hello
In this spreadsheet we have pairs of columns for each week-ending date. Let's say that week 1 uses Columns C&D. Each Row represents a store. Columns C and D both represent week-ending 6th October 2007. I need a formula which will look along the row and check for a blank. If Column C is blank and there is a figure in Column D, or vice-versa, I want the formula to flag this occurence. In other words, I want the spreadsheet to show me where a store has enetered a figure in one column but no figure in the paired column across the sheet. Can anyone suggest a formula please? Many thanks. Keith |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing figures across 2 columns.
Hi,
You could use just use Autofilter and select blanks from the list..(DataFilterAutofilter) Or.. Lets say your data starts in in Cell C1... Maybe in cell E1 enter =COUNTBLANK(C1:D1) and copy down has far as you need this will count the number of blank cells in the range. Hope this helps, Gav. "bollard" wrote: Hello In this spreadsheet we have pairs of columns for each week-ending date. Let's say that week 1 uses Columns C&D. Each Row represents a store. Columns C and D both represent week-ending 6th October 2007. I need a formula which will look along the row and check for a blank. If Column C is blank and there is a figure in Column D, or vice-versa, I want the formula to flag this occurence. In other words, I want the spreadsheet to show me where a store has enetered a figure in one column but no figure in the paired column across the sheet. Can anyone suggest a formula please? Many thanks. Keith |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing figures across 2 columns.
Hi Gav
Many thanks for your reply. I have been using the Autofilter, but it's very time consuming that way, not least because there are actually 2 spreadhseets and some 24 columns on each.. Also, I don't want to count the number of occurences, I want to find them and notify the store that they have made an error. "Gav123" wrote: Hi, You could use just use Autofilter and select blanks from the list..(DataFilterAutofilter) Or.. Lets say your data starts in in Cell C1... Maybe in cell E1 enter =COUNTBLANK(C1:D1) and copy down has far as you need this will count the number of blank cells in the range. Hope this helps, Gav. "bollard" wrote: Hello In this spreadsheet we have pairs of columns for each week-ending date. Let's say that week 1 uses Columns C&D. Each Row represents a store. Columns C and D both represent week-ending 6th October 2007. I need a formula which will look along the row and check for a blank. If Column C is blank and there is a figure in Column D, or vice-versa, I want the formula to flag this occurence. In other words, I want the spreadsheet to show me where a store has enetered a figure in one column but no figure in the paired column across the sheet. Can anyone suggest a formula please? Many thanks. Keith |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing figures across 2 columns.
The only other way I can think off without using a macro is using conditional
formatting.... select the first cell that you want to flag on your sheet... Then select FormatConditional Formatting.. Select Formula is from the first drop down and in the condition section type =ISBLANK(C1) and choose your format. Copy this along your 24 columns and down as far as you need. This will then make it easier to see what stores haven't entered the data. Still a bit time consuming I'm afraid... Gav. "bollard" wrote: Hi Gav Many thanks for your reply. I have been using the Autofilter, but it's very time consuming that way, not least because there are actually 2 spreadhseets and some 24 columns on each.. Also, I don't want to count the number of occurences, I want to find them and notify the store that they have made an error. "Gav123" wrote: Hi, You could use just use Autofilter and select blanks from the list..(DataFilterAutofilter) Or.. Lets say your data starts in in Cell C1... Maybe in cell E1 enter =COUNTBLANK(C1:D1) and copy down has far as you need this will count the number of blank cells in the range. Hope this helps, Gav. "bollard" wrote: Hello In this spreadsheet we have pairs of columns for each week-ending date. Let's say that week 1 uses Columns C&D. Each Row represents a store. Columns C and D both represent week-ending 6th October 2007. I need a formula which will look along the row and check for a blank. If Column C is blank and there is a figure in Column D, or vice-versa, I want the formula to flag this occurence. In other words, I want the spreadsheet to show me where a store has enetered a figure in one column but no figure in the paired column across the sheet. Can anyone suggest a formula please? Many thanks. Keith |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing figures across 2 columns.
Hi Keith,
You will have to adjust this to suit, but I think it will do what you want. Select cell C2 and set Conditional Formatting with Formula is =COUNTBLANK(C2:D2)=1 (pick a color) Then Select cell D2 and set Conditional Formatting with Formula is =COUNTBLANK(C2:D2)=1 (pick same color) (note same formula in each cell but applied seperately) Now highlight both cells and use the formula painter to drag them across the sheet. Then reselect the entire row grab the formula painter and once again select the entire row and drag down as far as is needed. It works in my trials here but you may need to adjust for your data HTH Martin "bollard" wrote in message ... Hello In this spreadsheet we have pairs of columns for each week-ending date. Let's say that week 1 uses Columns C&D. Each Row represents a store. Columns C and D both represent week-ending 6th October 2007. I need a formula which will look along the row and check for a blank. If Column C is blank and there is a figure in Column D, or vice-versa, I want the formula to flag this occurence. In other words, I want the spreadsheet to show me where a store has enetered a figure in one column but no figure in the paired column across the sheet. Can anyone suggest a formula please? Many thanks. Keith |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing figures across 2 columns.
Correction!!
Where I said formula painter, read format painter. "MartinW" wrote in message ... Hi Keith, You will have to adjust this to suit, but I think it will do what you want. Select cell C2 and set Conditional Formatting with Formula is =COUNTBLANK(C2:D2)=1 (pick a color) Then Select cell D2 and set Conditional Formatting with Formula is =COUNTBLANK(C2:D2)=1 (pick same color) (note same formula in each cell but applied seperately) Now highlight both cells and use the formula painter to drag them across the sheet. Then reselect the entire row grab the formula painter and once again select the entire row and drag down as far as is needed. It works in my trials here but you may need to adjust for your data HTH Martin "bollard" wrote in message ... Hello In this spreadsheet we have pairs of columns for each week-ending date. Let's say that week 1 uses Columns C&D. Each Row represents a store. Columns C and D both represent week-ending 6th October 2007. I need a formula which will look along the row and check for a blank. If Column C is blank and there is a figure in Column D, or vice-versa, I want the formula to flag this occurence. In other words, I want the spreadsheet to show me where a store has enetered a figure in one column but no figure in the paired column across the sheet. Can anyone suggest a formula please? Many thanks. Keith |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing figures across 2 columns.
Hi Martin
Many thanks for that. It works a treat! "MartinW" wrote: Correction!! Where I said formula painter, read format painter. "MartinW" wrote in message ... Hi Keith, You will have to adjust this to suit, but I think it will do what you want. Select cell C2 and set Conditional Formatting with Formula is =COUNTBLANK(C2:D2)=1 (pick a color) Then Select cell D2 and set Conditional Formatting with Formula is =COUNTBLANK(C2:D2)=1 (pick same color) (note same formula in each cell but applied seperately) Now highlight both cells and use the formula painter to drag them across the sheet. Then reselect the entire row grab the formula painter and once again select the entire row and drag down as far as is needed. It works in my trials here but you may need to adjust for your data HTH Martin "bollard" wrote in message ... Hello In this spreadsheet we have pairs of columns for each week-ending date. Let's say that week 1 uses Columns C&D. Each Row represents a store. Columns C and D both represent week-ending 6th October 2007. I need a formula which will look along the row and check for a blank. If Column C is blank and there is a figure in Column D, or vice-versa, I want the formula to flag this occurence. In other words, I want the spreadsheet to show me where a store has enetered a figure in one column but no figure in the paired column across the sheet. Can anyone suggest a formula please? Many thanks. Keith |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing figures across 2 columns.
Glad to hear that Keith, thanks for the feedback.
Martin "bollard" wrote in message ... Hi Martin Many thanks for that. It works a treat! "MartinW" wrote: Correction!! Where I said formula painter, read format painter. "MartinW" wrote in message ... Hi Keith, You will have to adjust this to suit, but I think it will do what you want. Select cell C2 and set Conditional Formatting with Formula is =COUNTBLANK(C2:D2)=1 (pick a color) Then Select cell D2 and set Conditional Formatting with Formula is =COUNTBLANK(C2:D2)=1 (pick same color) (note same formula in each cell but applied seperately) Now highlight both cells and use the formula painter to drag them across the sheet. Then reselect the entire row grab the formula painter and once again select the entire row and drag down as far as is needed. It works in my trials here but you may need to adjust for your data HTH Martin "bollard" wrote in message ... Hello In this spreadsheet we have pairs of columns for each week-ending date. Let's say that week 1 uses Columns C&D. Each Row represents a store. Columns C and D both represent week-ending 6th October 2007. I need a formula which will look along the row and check for a blank. If Column C is blank and there is a figure in Column D, or vice-versa, I want the formula to flag this occurence. In other words, I want the spreadsheet to show me where a store has enetered a figure in one column but no figure in the paired column across the sheet. Can anyone suggest a formula please? Many thanks. Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing two columns | Excel Discussion (Misc queries) | |||
Comparing two columns of information with 2 new columns of informa | Excel Discussion (Misc queries) | |||
average price of 4 columns of figures but some cells can be blank | Excel Worksheet Functions | |||
average price of 4 columns of figures but some cells can be blank | Excel Worksheet Functions | |||
Comparing 2 columns if they are the same | Excel Worksheet Functions |