Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Unique/Duplicates
I have a worksheet with last years jobs on it:
A B C D E F G Date Job # Salesman Client How Hours Status I need to update the worksheet with the same data, except it's possible that some of the information has been changed (i.e. salesman, status). Would the best way be to combine both lists together, sort on the Job # and just look through every two rows to see if there's a change or if the info stayed the same or is there some kind of formula I can use that would make this easier? TIA for any/all help/insight!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Unique/Duplicates
On Oct 20, 7:46 pm, IMS Lori
wrote: I have a worksheet with last years jobs on it: A B C D E F G Date Job # Salesman Client How Hours Status I need to update the worksheet with the same data, except it's possible that some of the information has been changed (i.e. salesman, status). Would the best way be to combine both lists together, sort on the Job # and just look through every two rows to see if there's a change or if the info stayed the same or is there some kind of formula I can use that would make this easier? TIA for any/all help/insight!!! Hi, If the data is identical, why not paste the data on two sheets in a workbook and then use a formula like the following in a third sheet to compare the cells in the two sheets =Sheet1!A1=Sheet4!A1 A "FALSE" would mean change. Hope that helps. Regards, Raj |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Unique/Duplicates
Lori
What you could do is use a simple IF to find the changes fast like this: =IF(Sheet1!A1=Sheet2!A1,0,1) Put this formula in the sheet where the changes are likely to occur, then you copy the formula to the exact number of cells of your job list. Anything that is different shows up as a "1". If you want to get really clever, set a conditional format to the cells containing the above formula that if the cell = 1 then it must be highlighted yellow. If this helps please click "Yes" <<<<<<<<<<< "IMS Lori" wrote: I have a worksheet with last years jobs on it: A B C D E F G Date Job # Salesman Client How Hours Status I need to update the worksheet with the same data, except it's possible that some of the information has been changed (i.e. salesman, status). Would the best way be to combine both lists together, sort on the Job # and just look through every two rows to see if there's a change or if the info stayed the same or is there some kind of formula I can use that would make this easier? TIA for any/all help/insight!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Unique/Duplicates
Only problem is that only checks column A...
Is there a way to get it so that one formula checks columns A through F? Right now I just dragged the formula out 5 more times and it's working, but I have 3 more years of data to go through and I have to do this again next year. Thanks so much for your help!!!!! "BSc Chem Eng Rick" wrote: Lori What you could do is use a simple IF to find the changes fast like this: =IF(Sheet1!A1=Sheet2!A1,0,1) Put this formula in the sheet where the changes are likely to occur, then you copy the formula to the exact number of cells of your job list. Anything that is different shows up as a "1". If you want to get really clever, set a conditional format to the cells containing the above formula that if the cell = 1 then it must be highlighted yellow. If this helps please click "Yes" <<<<<<<<<<< "IMS Lori" wrote: I have a worksheet with last years jobs on it: A B C D E F G Date Job # Salesman Client How Hours Status I need to update the worksheet with the same data, except it's possible that some of the information has been changed (i.e. salesman, status). Would the best way be to combine both lists together, sort on the Job # and just look through every two rows to see if there's a change or if the info stayed the same or is there some kind of formula I can use that would make this easier? TIA for any/all help/insight!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Unique/Duplicates
On Oct 20, 10:58 pm, IMS Lori
wrote: Only problem is that only checks column A... Is there a way to get it so that one formula checks columns A through F? Right now I just dragged the formula out 5 more times and it's working, but I have 3 more years of data to go through and I have to do this again next year. Thanks so much for your help!!!!! "BSc Chem Eng Rick" wrote: Lori What you could do is use a simple IF to find the changes fast like this: =IF(Sheet1!A1=Sheet2!A1,0,1) Put this formula in the sheet where the changes are likely to occur, then you copy the formula to the exact number of cells of your job list. Anything that is different shows up as a "1". If you want to get really clever, set a conditional format to the cells containing the above formula that if the cell = 1 then it must be highlighted yellow. If this helps please click "Yes" <<<<<<<<<<< "IMS Lori" wrote: I have a worksheet with last years jobs on it: A B C D E F G Date Job # Salesman Client How Hours Status I need to update the worksheet with the same data, except it's possible that some of the information has been changed (i.e. salesman, status). Would the best way be to combine both lists together, sort on the Job # and just look through every two rows to see if there's a change or if the info stayed the same or is there some kind of formula I can use that would make this easier? TIA for any/all help/insight!!! For filling up large parts of the sheet with formulas, try the following: Enter the formula in cell a1 Select the left top corner cell (eg. A1) and then use Edit -Go To-- Input the right bottom cell in the reference box and keeping "shift" key pressed, click on ok. This will select the entire range of cells With the entire range selected, use Fill-down and Fill-right to fill the sheet with the formula Regards, Raj |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Unique/Duplicates
Lori Try this
=IF(Sheet1!A:F=Sheet2!A:F,0,1) This is an array formula so once you've entered it you must press Ctrl+Shift+Enter to make it work. So what you do is highlight enough cells to cover all the entries in your job list. Type the above formula in the top-left of this highlighted range and then hit Ctrl+Shift+Enter. If this helps, please click "Yes" <<<<<<<<<<<< "IMS Lori" wrote: Only problem is that only checks column A... Is there a way to get it so that one formula checks columns A through F? Right now I just dragged the formula out 5 more times and it's working, but I have 3 more years of data to go through and I have to do this again next year. Thanks so much for your help!!!!! "BSc Chem Eng Rick" wrote: Lori What you could do is use a simple IF to find the changes fast like this: =IF(Sheet1!A1=Sheet2!A1,0,1) Put this formula in the sheet where the changes are likely to occur, then you copy the formula to the exact number of cells of your job list. Anything that is different shows up as a "1". If you want to get really clever, set a conditional format to the cells containing the above formula that if the cell = 1 then it must be highlighted yellow. If this helps please click "Yes" <<<<<<<<<<< "IMS Lori" wrote: I have a worksheet with last years jobs on it: A B C D E F G Date Job # Salesman Client How Hours Status I need to update the worksheet with the same data, except it's possible that some of the information has been changed (i.e. salesman, status). Would the best way be to combine both lists together, sort on the Job # and just look through every two rows to see if there's a change or if the info stayed the same or is there some kind of formula I can use that would make this easier? TIA for any/all help/insight!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicates and unique values | Excel Worksheet Functions | |||
Finding duplicates | Excel Discussion (Misc queries) | |||
Finding duplicates | Excel Discussion (Misc queries) | |||
Finding duplicates | Excel Worksheet Functions | |||
Duplicates - deleting to get just unique ones | New Users to Excel |