Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 sets of data
I have 2 sets of data that i will place side by side and each record will
display on each row rather than columns. They will consist of the the same amount of columns across. Example - ID, Last Name, First Name, Enter Date, disability Code placed on row 1 I want to compare the first range of 5 columns on the first row to the next 5 columns on the same row. Ideally i would like to have an X placed on the 11th column as a sign of a discrepancy in data (If enter date in the first range is different from the second set or Disability Code is different and so on). Would that be possible or would it be simpler to have something where the different fields are highlighted. If D1 does not equal I1 or E1 does not equal J1. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 sets of data
I just made up this long formula but it's giving me an error on the last
"IF". Also i think i have too many expressions to compare. =IF(A2=J2," ","X",IF(B2=K2," ","X",IF(C2=L2," ","X",IF(D2=M2," ","X",IF(E2=N2," ","X",IF(F2=O2," ","X",IF(G2=P2," ","X",IF(H2=Q2," ","X",IF(I2=R2," ","X")))))))))) "Juan" wrote: I have 2 sets of data that i will place side by side and each record will display on each row rather than columns. They will consist of the the same amount of columns across. Example - ID, Last Name, First Name, Enter Date, disability Code placed on row 1 I want to compare the first range of 5 columns on the first row to the next 5 columns on the same row. Ideally i would like to have an X placed on the 11th column as a sign of a discrepancy in data (If enter date in the first range is different from the second set or Disability Code is different and so on). Would that be possible or would it be simpler to have something where the different fields are highlighted. If D1 does not equal I1 or E1 does not equal J1. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 sets of data
Hi Juan,
here is the formula that will enter the X if any of the values doesn't match =+IF(AND(A3=F3,B3=G3,C3=H3,D3=I3,E3=J3),"","X") If you want to highlight the cell where the information doesn't match use the conditional formating "Juan" wrote: I have 2 sets of data that i will place side by side and each record will display on each row rather than columns. They will consist of the the same amount of columns across. Example - ID, Last Name, First Name, Enter Date, disability Code placed on row 1 I want to compare the first range of 5 columns on the first row to the next 5 columns on the same row. Ideally i would like to have an X placed on the 11th column as a sign of a discrepancy in data (If enter date in the first range is different from the second set or Disability Code is different and so on). Would that be possible or would it be simpler to have something where the different fields are highlighted. If D1 does not equal I1 or E1 does not equal J1. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 sets of data
Wow thank you Eduardo, that worked. Although i tried a conditional formual to
highlighted each cell that is different between the 2 ranges and it seems to compared the cell on one row to the cell on the row above from the other range. I used the the formulas below with the named ranges of Aeries and SEIS. =COUNTIF(SEIS,A2)=0 =COUNTIF(Aeries,A2)=0 "Eduardo" wrote: Hi Juan, here is the formula that will enter the X if any of the values doesn't match =+IF(AND(A3=F3,B3=G3,C3=H3,D3=I3,E3=J3),"","X") If you want to highlight the cell where the information doesn't match use the conditional formating "Juan" wrote: I have 2 sets of data that i will place side by side and each record will display on each row rather than columns. They will consist of the the same amount of columns across. Example - ID, Last Name, First Name, Enter Date, disability Code placed on row 1 I want to compare the first range of 5 columns on the first row to the next 5 columns on the same row. Ideally i would like to have an X placed on the 11th column as a sign of a discrepancy in data (If enter date in the first range is different from the second set or Disability Code is different and so on). Would that be possible or would it be simpler to have something where the different fields are highlighted. If D1 does not equal I1 or E1 does not equal J1. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 sets of data
Hi Juan,
If you want to compare column A with F, you have to be positioned in Column F, go to conditional formating, enter where the cell vallue is not equal to A1, then go to format and choose fill and select the color you want press ok , you should be able to see in preview the color choosen, then press ok again. if the value doesn't match it will color the cell. Do once for each column in the second serie of data and then copy the format down to the last cell "Juan" wrote: Wow thank you Eduardo, that worked. Although i tried a conditional formual to highlighted each cell that is different between the 2 ranges and it seems to compared the cell on one row to the cell on the row above from the other range. I used the the formulas below with the named ranges of Aeries and SEIS. =COUNTIF(SEIS,A2)=0 =COUNTIF(Aeries,A2)=0 "Eduardo" wrote: Hi Juan, here is the formula that will enter the X if any of the values doesn't match =+IF(AND(A3=F3,B3=G3,C3=H3,D3=I3,E3=J3),"","X") If you want to highlight the cell where the information doesn't match use the conditional formating "Juan" wrote: I have 2 sets of data that i will place side by side and each record will display on each row rather than columns. They will consist of the the same amount of columns across. Example - ID, Last Name, First Name, Enter Date, disability Code placed on row 1 I want to compare the first range of 5 columns on the first row to the next 5 columns on the same row. Ideally i would like to have an X placed on the 11th column as a sign of a discrepancy in data (If enter date in the first range is different from the second set or Disability Code is different and so on). Would that be possible or would it be simpler to have something where the different fields are highlighted. If D1 does not equal I1 or E1 does not equal J1. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 sets of data
Juan, I have to leave now if you need more help I will try to come to you
before monday "Juan" wrote: Wow thank you Eduardo, that worked. Although i tried a conditional formual to highlighted each cell that is different between the 2 ranges and it seems to compared the cell on one row to the cell on the row above from the other range. I used the the formulas below with the named ranges of Aeries and SEIS. =COUNTIF(SEIS,A2)=0 =COUNTIF(Aeries,A2)=0 "Eduardo" wrote: Hi Juan, here is the formula that will enter the X if any of the values doesn't match =+IF(AND(A3=F3,B3=G3,C3=H3,D3=I3,E3=J3),"","X") If you want to highlight the cell where the information doesn't match use the conditional formating "Juan" wrote: I have 2 sets of data that i will place side by side and each record will display on each row rather than columns. They will consist of the the same amount of columns across. Example - ID, Last Name, First Name, Enter Date, disability Code placed on row 1 I want to compare the first range of 5 columns on the first row to the next 5 columns on the same row. Ideally i would like to have an X placed on the 11th column as a sign of a discrepancy in data (If enter date in the first range is different from the second set or Disability Code is different and so on). Would that be possible or would it be simpler to have something where the different fields are highlighted. If D1 does not equal I1 or E1 does not equal J1. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 sets of data
Thank you i will try this method. I just thought there would be an easier way
to select the entire range A2:I91 to compare to the other range J2:R91. Thanks again. "Eduardo" wrote: Hi Juan, If you want to compare column A with F, you have to be positioned in Column F, go to conditional formating, enter where the cell vallue is not equal to A1, then go to format and choose fill and select the color you want press ok , you should be able to see in preview the color choosen, then press ok again. if the value doesn't match it will color the cell. Do once for each column in the second serie of data and then copy the format down to the last cell "Juan" wrote: Wow thank you Eduardo, that worked. Although i tried a conditional formual to highlighted each cell that is different between the 2 ranges and it seems to compared the cell on one row to the cell on the row above from the other range. I used the the formulas below with the named ranges of Aeries and SEIS. =COUNTIF(SEIS,A2)=0 =COUNTIF(Aeries,A2)=0 "Eduardo" wrote: Hi Juan, here is the formula that will enter the X if any of the values doesn't match =+IF(AND(A3=F3,B3=G3,C3=H3,D3=I3,E3=J3),"","X") If you want to highlight the cell where the information doesn't match use the conditional formating "Juan" wrote: I have 2 sets of data that i will place side by side and each record will display on each row rather than columns. They will consist of the the same amount of columns across. Example - ID, Last Name, First Name, Enter Date, disability Code placed on row 1 I want to compare the first range of 5 columns on the first row to the next 5 columns on the same row. Ideally i would like to have an X placed on the 11th column as a sign of a discrepancy in data (If enter date in the first range is different from the second set or Disability Code is different and so on). Would that be possible or would it be simpler to have something where the different fields are highlighted. If D1 does not equal I1 or E1 does not equal J1. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 sets of data
Hi Juan, if it was helpful to you could you please rate it where it says if
the post was helpful to you, thank you "Juan" wrote: Thank you i will try this method. I just thought there would be an easier way to select the entire range A2:I91 to compare to the other range J2:R91. Thanks again. "Eduardo" wrote: Hi Juan, If you want to compare column A with F, you have to be positioned in Column F, go to conditional formating, enter where the cell vallue is not equal to A1, then go to format and choose fill and select the color you want press ok , you should be able to see in preview the color choosen, then press ok again. if the value doesn't match it will color the cell. Do once for each column in the second serie of data and then copy the format down to the last cell "Juan" wrote: Wow thank you Eduardo, that worked. Although i tried a conditional formual to highlighted each cell that is different between the 2 ranges and it seems to compared the cell on one row to the cell on the row above from the other range. I used the the formulas below with the named ranges of Aeries and SEIS. =COUNTIF(SEIS,A2)=0 =COUNTIF(Aeries,A2)=0 "Eduardo" wrote: Hi Juan, here is the formula that will enter the X if any of the values doesn't match =+IF(AND(A3=F3,B3=G3,C3=H3,D3=I3,E3=J3),"","X") If you want to highlight the cell where the information doesn't match use the conditional formating "Juan" wrote: I have 2 sets of data that i will place side by side and each record will display on each row rather than columns. They will consist of the the same amount of columns across. Example - ID, Last Name, First Name, Enter Date, disability Code placed on row 1 I want to compare the first range of 5 columns on the first row to the next 5 columns on the same row. Ideally i would like to have an X placed on the 11th column as a sign of a discrepancy in data (If enter date in the first range is different from the second set or Disability Code is different and so on). Would that be possible or would it be simpler to have something where the different fields are highlighted. If D1 does not equal I1 or E1 does not equal J1. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel graph 3 sets of data. 2 sets as lines 1 as column? | Charts and Charting in Excel | |||
show 3 sets of data per data point in a scatter plot | Charts and Charting in Excel | |||
Compare 2 sets of data | Excel Discussion (Misc queries) | |||
How do I compare 2 sets of data and highlight differences? | Excel Worksheet Functions | |||
How can I compare 2 sets of Social Security #'s and Identify dupes | Excel Discussion (Misc queries) |