|
simplify function
I created this function to show you exactly what I need. I know if I plug it
in it will return a #VALUE# error. I tried to create it without the OR() function but it is too long for Excel. Is there a more simple way to write this? Sheet1: A B C D E F G H I J K 1 2 2 8 8 8 8 should show 88 3 1 0 5 9 5 1 should show 15 4 8 3 4 5 2 4 should show "" =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE(D2,E2),CONCATENATE(E2,C2 ),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCAT ENATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2) ,CONCATENATE(K2,I2),CONCATENATE(K2,J2)),I2,"") Thank you for your time Luke |
I made a mistake and put a mutiplication sign in the formula. The correct
formula: =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE(D2,E2),CONCATENATE(E2,C2 ),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCAT ENATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2) ,CONCATENATE(K2,I2),CONCATENATE(K2,J2)),I2,"") Not sure how that "*" sign got in there. sorry Regards, Luke "Luke" wrote: I created this function to show you exactly what I need. I know if I plug it in it will return a #VALUE# error. I tried to create it without the OR() function but it is too long for Excel. Is there a more simple way to write this? Sheet1: A B C D E F G H I J K 1 2 2 8 8 8 8 should show 88 3 1 0 5 9 5 1 should show 15 4 8 3 4 5 2 4 should show "" =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE(D2,E2),CONCATENATE(E2,C2 ),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCAT ENATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2) ,CONCATENATE(K2,I2),CONCATENATE(K2,J2)),I2,"") Thank you for your time Luke |
Can you explain why it should be 88, 15 "" as I am afraid the formula makes
no sense (to me) -- HTH Bob Phillips "Luke" wrote in message ... I made a mistake and put a mutiplication sign in the formula. The correct formula: =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Not sure how that "*" sign got in there. sorry Regards, Luke "Luke" wrote: I created this function to show you exactly what I need. I know if I plug it in it will return a #VALUE# error. I tried to create it without the OR() function but it is too long for Excel. Is there a more simple way to write this? Sheet1: A B C D E F G H I J K 1 2 2 8 8 8 8 should show 88 3 1 0 5 9 5 1 should show 15 4 8 3 4 5 2 4 should show "" =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Thank you for your time Luke |
Hi Bob:
My interpretation: if any two of the numbers in the first group match any two of the number in the second group, concatenate the two matching numbers in the first group. I assume that if all three numbers match, you would concatenate the first two in the first group. It's an interesting challenge but I had to get back to real work. I had it working except for the case where there is a duplicate entry in the first group. Regards, Vasant "Bob Phillips" wrote in message ... Can you explain why it should be 88, 15 "" as I am afraid the formula makes no sense (to me) -- HTH Bob Phillips "Luke" wrote in message ... I made a mistake and put a mutiplication sign in the formula. The correct formula: =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Not sure how that "*" sign got in there. sorry Regards, Luke "Luke" wrote: I created this function to show you exactly what I need. I know if I plug it in it will return a #VALUE# error. I tried to create it without the OR() function but it is too long for Excel. Is there a more simple way to write this? Sheet1: A B C D E F G H I J K 1 2 2 8 8 8 8 should show 88 3 1 0 5 9 5 1 should show 15 4 8 3 4 5 2 4 should show "" =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Thank you for your time Luke |
Opps again I made an error on this table... It should have read:
A B C D E F G H I J K 1 2 2 8 8 8 8 G2 should show 8 3 1 0 5 9 5 1 G3 should show "" 4 8 3 4 5 2 4 G4 should show "" I should have pointed out that in this example I want I2 to show up in G2 but only if any combination of two or more cells in I2 J2 and K2 are equal to any combination of two or more cells in C2 D2 and E2. each (C2,D2,E2) and (I2,J2,K2) has 6 different combinations totaling 36 different senarios. So, if I concatenate cell C2 and D2 they don't equal any concatenated combination of any two given cells in I2,J2 & k2. However if I concatenate(D2,E2) they are the same as Concatenate(I2,K2). I'll elaborate with that way too long formula of which I broke up into 6 segments so it is easir to see: =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2, IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(con catenate(C2,E2)=concatenate(I2,K2),I2,IF(concatena te(C2,E2)=concatenate(J2,I2),I2,IF(concatenate(C2, E2)=concatenate(J2,K2),I2,IF(concatenate(C2,E2)=co ncatenate(K2,I2),I2,IF(concatenate(C2,E2)=concaten ate(K2,J2),I2, IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(con catenate(D2,C2)=concatenate(I2,K2),I2,IF(concatena te(D2,C2)=concatenate(J2,I2),I2,IF(concatenate(D2, C2)=concatenate(J2,K2),I2,IF(concatenate(D2,C2)=co ncatenate(K2,I2),I2,IF(concatenate(D2,C2)=concaten ate(K2,J2),I2, IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(con catenate(D2,E2)=concatenate(I2,K2),I2,IF(concatena te(D2,E2)=concatenate(J2,I2),I2,IF(concatenate(D2, E2)=concatenate(J2,K2),I2,IF(concatenate(D2,E2)=co ncatenate(K2,I2),I2,IF(concatenate(D2,E2)=concaten ate(K2,J2),I2, IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(con catenate(E2,C2)=concatenate(I2,K2),I2,IF(concatena te(E2,C2)=concatenate(J2,I2),I2,IF(concatenate(E2, C2)=concatenate(J2,K2),I2,IF(concatenate(E2,C2)=co ncatenate(K2,I2),I2,IF(concatenate(E2,C2)=concaten ate(K2,J2),I2, IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(con catenate(E2,D2)=concatenate(I2,K2),I2,IF(concatena te(E2,D2)=concatenate(J2,I2),I2,IF(concatenate(E2, D2)=concatenate(J2,K2),I2,IF(concatenate(E2,D2)=co ncatenate(K2,I2),I2,IF(concatenate(E2,D2)=concaten ate(K2,J2),I2,""))))))))))))))))))))... I think that is about it... if this don't confound the brain I don't know what will:) Thank you Bob for your responses "Bob Phillips" wrote: Can you explain why it should be 88, 15 "" as I am afraid the formula makes no sense (to me) -- HTH Bob Phillips "Luke" wrote in message ... I made a mistake and put a mutiplication sign in the formula. The correct formula: =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Not sure how that "*" sign got in there. sorry Regards, Luke "Luke" wrote: I created this function to show you exactly what I need. I know if I plug it in it will return a #VALUE# error. I tried to create it without the OR() function but it is too long for Excel. Is there a more simple way to write this? Sheet1: A B C D E F G H I J K 1 2 2 8 8 8 8 should show 88 3 1 0 5 9 5 1 should show 15 4 8 3 4 5 2 4 should show "" =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Thank you for your time Luke |
Still isn't right. I2 is "", not 8.
-- Vasant "Luke" wrote in message ... Opps again I made an error on this table... It should have read: A B C D E F G H I J K 1 2 2 8 8 8 8 G2 should show 8 3 1 0 5 9 5 1 G3 should show "" 4 8 3 4 5 2 4 G4 should show "" I should have pointed out that in this example I want I2 to show up in G2 but only if any combination of two or more cells in I2 J2 and K2 are equal to any combination of two or more cells in C2 D2 and E2. each (C2,D2,E2) and (I2,J2,K2) has 6 different combinations totaling 36 different senarios. So, if I concatenate cell C2 and D2 they don't equal any concatenated combination of any two given cells in I2,J2 & k2. However if I concatenate(D2,E2) they are the same as Concatenate(I2,K2). I'll elaborate with that way too long formula of which I broke up into 6 segments so it is easir to see: =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2, IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(con catenate(C2,E2)=concatenat e(I2,K2),I2,IF(concatenate(C2,E2)=concatenate(J2,I 2),I2,IF(concatenate(C2,E2 )=concatenate(J2,K2),I2,IF(concatenate(C2,E2)=conc atenate(K2,I2),I2,IF(conca tenate(C2,E2)=concatenate(K2,J2),I2, IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(con catenate(D2,C2)=concatenat e(I2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I 2),I2,IF(concatenate(D2,C2 )=concatenate(J2,K2),I2,IF(concatenate(D2,C2)=conc atenate(K2,I2),I2,IF(conca tenate(D2,C2)=concatenate(K2,J2),I2, IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(con catenate(D2,E2)=concatenat e(I2,K2),I2,IF(concatenate(D2,E2)=concatenate(J2,I 2),I2,IF(concatenate(D2,E2 )=concatenate(J2,K2),I2,IF(concatenate(D2,E2)=conc atenate(K2,I2),I2,IF(conca tenate(D2,E2)=concatenate(K2,J2),I2, IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(con catenate(E2,C2)=concatenat e(I2,K2),I2,IF(concatenate(E2,C2)=concatenate(J2,I 2),I2,IF(concatenate(E2,C2 )=concatenate(J2,K2),I2,IF(concatenate(E2,C2)=conc atenate(K2,I2),I2,IF(conca tenate(E2,C2)=concatenate(K2,J2),I2, IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(con catenate(E2,D2)=concatenat e(I2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I 2),I2,IF(concatenate(E2,D2 )=concatenate(J2,K2),I2,IF(concatenate(E2,D2)=conc atenate(K2,I2),I2,IF(conca tenate(E2,D2)=concatenate(K2,J2),I2,"")))))))))))) ))))))))... I think that is about it... if this don't confound the brain I don't know what will:) Thank you Bob for your responses "Bob Phillips" wrote: Can you explain why it should be 88, 15 "" as I am afraid the formula makes no sense (to me) -- HTH Bob Phillips "Luke" wrote in message ... I made a mistake and put a mutiplication sign in the formula. The correct formula: =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Not sure how that "*" sign got in there. sorry Regards, Luke "Luke" wrote: I created this function to show you exactly what I need. I know if I plug it in it will return a #VALUE# error. I tried to create it without the OR() function but it is too long for Excel. Is there a more simple way to write this? Sheet1: A B C D E F G H I J K 1 2 2 8 8 8 8 should show 88 3 1 0 5 9 5 1 should show 15 4 8 3 4 5 2 4 should show "" =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Thank you for your time Luke |
Hi Luke,
Try the following formula in G2 =IF(OR(ISNA(MODE(C2:E2)),ISNA(MODE(H2:J2))),"",IF( MODE(C2:E2)=MODE(H2:J2),I2 ,"")) Alok "Luke" wrote in message ... Opps again I made an error on this table... It should have read: A B C D E F G H I J K 1 2 2 8 8 8 8 G2 should show 8 3 1 0 5 9 5 1 G3 should show "" 4 8 3 4 5 2 4 G4 should show "" I should have pointed out that in this example I want I2 to show up in G2 but only if any combination of two or more cells in I2 J2 and K2 are equal to any combination of two or more cells in C2 D2 and E2. each (C2,D2,E2) and (I2,J2,K2) has 6 different combinations totaling 36 different senarios. So, if I concatenate cell C2 and D2 they don't equal any concatenated combination of any two given cells in I2,J2 & k2. However if I concatenate(D2,E2) they are the same as Concatenate(I2,K2). I'll elaborate with that way too long formula of which I broke up into 6 segments so it is easir to see: =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2, IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(con catenate(C2,E2)=concatenat e(I2,K2),I2,IF(concatenate(C2,E2)=concatenate(J2,I 2),I2,IF(concatenate(C2,E2 )=concatenate(J2,K2),I2,IF(concatenate(C2,E2)=conc atenate(K2,I2),I2,IF(conca tenate(C2,E2)=concatenate(K2,J2),I2, IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(con catenate(D2,C2)=concatenat e(I2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I 2),I2,IF(concatenate(D2,C2 )=concatenate(J2,K2),I2,IF(concatenate(D2,C2)=conc atenate(K2,I2),I2,IF(conca tenate(D2,C2)=concatenate(K2,J2),I2, IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(con catenate(D2,E2)=concatenat e(I2,K2),I2,IF(concatenate(D2,E2)=concatenate(J2,I 2),I2,IF(concatenate(D2,E2 )=concatenate(J2,K2),I2,IF(concatenate(D2,E2)=conc atenate(K2,I2),I2,IF(conca tenate(D2,E2)=concatenate(K2,J2),I2, IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(con catenate(E2,C2)=concatenat e(I2,K2),I2,IF(concatenate(E2,C2)=concatenate(J2,I 2),I2,IF(concatenate(E2,C2 )=concatenate(J2,K2),I2,IF(concatenate(E2,C2)=conc atenate(K2,I2),I2,IF(conca tenate(E2,C2)=concatenate(K2,J2),I2, IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(con catenate(E2,D2)=concatenat e(I2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I 2),I2,IF(concatenate(E2,D2 )=concatenate(J2,K2),I2,IF(concatenate(E2,D2)=conc atenate(K2,I2),I2,IF(conca tenate(E2,D2)=concatenate(K2,J2),I2,"")))))))))))) ))))))))... I think that is about it... if this don't confound the brain I don't know what will:) Thank you Bob for your responses "Bob Phillips" wrote: Can you explain why it should be 88, 15 "" as I am afraid the formula makes no sense (to me) -- HTH Bob Phillips "Luke" wrote in message ... I made a mistake and put a mutiplication sign in the formula. The correct formula: =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Not sure how that "*" sign got in there. sorry Regards, Luke "Luke" wrote: I created this function to show you exactly what I need. I know if I plug it in it will return a #VALUE# error. I tried to create it without the OR() function but it is too long for Excel. Is there a more simple way to write this? Sheet1: A B C D E F G H I J K 1 2 2 8 8 8 8 should show 88 3 1 0 5 9 5 1 should show 15 4 8 3 4 5 2 4 should show "" =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Thank you for your time Luke |
in the example
C2=2 D2=8 E2=8 I2=8 J2="" K2=8 Sorry if they are not lined up. I2 is 8 so concatenate(I2,K2)=concatenate(D2,E2) and therefore G2 should show 8 regards Luke "Vasant Nanavati" wrote: Still isn't right. I2 is "", not 8. -- Vasant "Luke" wrote in message ... Opps again I made an error on this table... It should have read: A B C D E F G H I J K 1 2 2 8 8 8 8 G2 should show 8 3 1 0 5 9 5 1 G3 should show "" 4 8 3 4 5 2 4 G4 should show "" I should have pointed out that in this example I want I2 to show up in G2 but only if any combination of two or more cells in I2 J2 and K2 are equal to any combination of two or more cells in C2 D2 and E2. each (C2,D2,E2) and (I2,J2,K2) has 6 different combinations totaling 36 different senarios. So, if I concatenate cell C2 and D2 they don't equal any concatenated combination of any two given cells in I2,J2 & k2. However if I concatenate(D2,E2) they are the same as Concatenate(I2,K2). I'll elaborate with that way too long formula of which I broke up into 6 segments so it is easir to see: =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2, IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(con catenate(C2,E2)=concatenat e(I2,K2),I2,IF(concatenate(C2,E2)=concatenate(J2,I 2),I2,IF(concatenate(C2,E2 )=concatenate(J2,K2),I2,IF(concatenate(C2,E2)=conc atenate(K2,I2),I2,IF(conca tenate(C2,E2)=concatenate(K2,J2),I2, IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(con catenate(D2,C2)=concatenat e(I2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I 2),I2,IF(concatenate(D2,C2 )=concatenate(J2,K2),I2,IF(concatenate(D2,C2)=conc atenate(K2,I2),I2,IF(conca tenate(D2,C2)=concatenate(K2,J2),I2, IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(con catenate(D2,E2)=concatenat e(I2,K2),I2,IF(concatenate(D2,E2)=concatenate(J2,I 2),I2,IF(concatenate(D2,E2 )=concatenate(J2,K2),I2,IF(concatenate(D2,E2)=conc atenate(K2,I2),I2,IF(conca tenate(D2,E2)=concatenate(K2,J2),I2, IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(con catenate(E2,C2)=concatenat e(I2,K2),I2,IF(concatenate(E2,C2)=concatenate(J2,I 2),I2,IF(concatenate(E2,C2 )=concatenate(J2,K2),I2,IF(concatenate(E2,C2)=conc atenate(K2,I2),I2,IF(conca tenate(E2,C2)=concatenate(K2,J2),I2, IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(con catenate(E2,D2)=concatenat e(I2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I 2),I2,IF(concatenate(E2,D2 )=concatenate(J2,K2),I2,IF(concatenate(E2,D2)=conc atenate(K2,I2),I2,IF(conca tenate(E2,D2)=concatenate(K2,J2),I2,"")))))))))))) ))))))))... I think that is about it... if this don't confound the brain I don't know what will:) Thank you Bob for your responses "Bob Phillips" wrote: Can you explain why it should be 88, 15 "" as I am afraid the formula makes no sense (to me) -- HTH Bob Phillips "Luke" wrote in message ... I made a mistake and put a mutiplication sign in the formula. The correct formula: =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Not sure how that "*" sign got in there. sorry Regards, Luke "Luke" wrote: I created this function to show you exactly what I need. I know if I plug it in it will return a #VALUE# error. I tried to create it without the OR() function but it is too long for Excel. Is there a more simple way to write this? Sheet1: A B C D E F G H I J K 1 2 2 8 8 8 8 should show 88 3 1 0 5 9 5 1 should show 15 4 8 3 4 5 2 4 should show "" =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Thank you for your time Luke |
That works if I don't offer up a different combination with the same
digits... ie if I change I2 from 8 to "" and J2 from "" to 8 I get "0" "Alok Joshi" wrote: Hi Luke, Try the following formula in G2 =IF(OR(ISNA(MODE(C2:E2)),ISNA(MODE(H2:J2))),"",IF( MODE(C2:E2)=MODE(H2:J2),I2 ,"")) Alok "Luke" wrote in message ... Opps again I made an error on this table... It should have read: A B C D E F G H I J K 1 2 2 8 8 8 8 G2 should show 8 3 1 0 5 9 5 1 G3 should show "" 4 8 3 4 5 2 4 G4 should show "" I should have pointed out that in this example I want I2 to show up in G2 but only if any combination of two or more cells in I2 J2 and K2 are equal to any combination of two or more cells in C2 D2 and E2. each (C2,D2,E2) and (I2,J2,K2) has 6 different combinations totaling 36 different senarios. So, if I concatenate cell C2 and D2 they don't equal any concatenated combination of any two given cells in I2,J2 & k2. However if I concatenate(D2,E2) they are the same as Concatenate(I2,K2). I'll elaborate with that way too long formula of which I broke up into 6 segments so it is easir to see: =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2, IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(con catenate(C2,E2)=concatenat e(I2,K2),I2,IF(concatenate(C2,E2)=concatenate(J2,I 2),I2,IF(concatenate(C2,E2 )=concatenate(J2,K2),I2,IF(concatenate(C2,E2)=conc atenate(K2,I2),I2,IF(conca tenate(C2,E2)=concatenate(K2,J2),I2, IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(con catenate(D2,C2)=concatenat e(I2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I 2),I2,IF(concatenate(D2,C2 )=concatenate(J2,K2),I2,IF(concatenate(D2,C2)=conc atenate(K2,I2),I2,IF(conca tenate(D2,C2)=concatenate(K2,J2),I2, IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(con catenate(D2,E2)=concatenat e(I2,K2),I2,IF(concatenate(D2,E2)=concatenate(J2,I 2),I2,IF(concatenate(D2,E2 )=concatenate(J2,K2),I2,IF(concatenate(D2,E2)=conc atenate(K2,I2),I2,IF(conca tenate(D2,E2)=concatenate(K2,J2),I2, IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(con catenate(E2,C2)=concatenat e(I2,K2),I2,IF(concatenate(E2,C2)=concatenate(J2,I 2),I2,IF(concatenate(E2,C2 )=concatenate(J2,K2),I2,IF(concatenate(E2,C2)=conc atenate(K2,I2),I2,IF(conca tenate(E2,C2)=concatenate(K2,J2),I2, IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(con catenate(E2,D2)=concatenat e(I2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I 2),I2,IF(concatenate(E2,D2 )=concatenate(J2,K2),I2,IF(concatenate(E2,D2)=conc atenate(K2,I2),I2,IF(conca tenate(E2,D2)=concatenate(K2,J2),I2,"")))))))))))) ))))))))... I think that is about it... if this don't confound the brain I don't know what will:) Thank you Bob for your responses "Bob Phillips" wrote: Can you explain why it should be 88, 15 "" as I am afraid the formula makes no sense (to me) -- HTH Bob Phillips "Luke" wrote in message ... I made a mistake and put a mutiplication sign in the formula. The correct formula: =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Not sure how that "*" sign got in there. sorry Regards, Luke "Luke" wrote: I created this function to show you exactly what I need. I know if I plug it in it will return a #VALUE# error. I tried to create it without the OR() function but it is too long for Excel. Is there a more simple way to write this? Sheet1: A B C D E F G H I J K 1 2 2 8 8 8 8 should show 88 3 1 0 5 9 5 1 should show 15 4 8 3 4 5 2 4 should show "" =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Thank you for your time Luke |
Still confused. Shouldn't G3 show 9, then?
-- Vasant "Luke" wrote in message ... in the example C2=2 D2=8 E2=8 I2=8 J2="" K2=8 Sorry if they are not lined up. I2 is 8 so concatenate(I2,K2)=concatenate(D2,E2) and therefore G2 should show 8 regards Luke "Vasant Nanavati" wrote: Still isn't right. I2 is "", not 8. -- Vasant "Luke" wrote in message ... Opps again I made an error on this table... It should have read: A B C D E F G H I J K 1 2 2 8 8 8 8 G2 should show 8 3 1 0 5 9 5 1 G3 should show "" 4 8 3 4 5 2 4 G4 should show "" I should have pointed out that in this example I want I2 to show up in G2 but only if any combination of two or more cells in I2 J2 and K2 are equal to any combination of two or more cells in C2 D2 and E2. each (C2,D2,E2) and (I2,J2,K2) has 6 different combinations totaling 36 different senarios. So, if I concatenate cell C2 and D2 they don't equal any concatenated combination of any two given cells in I2,J2 & k2. However if I concatenate(D2,E2) they are the same as Concatenate(I2,K2). I'll elaborate with that way too long formula of which I broke up into 6 segments so it is easir to see: =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2, IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(con catenate(C2,E2)=concatenat e(I2,K2),I2,IF(concatenate(C2,E2)=concatenate(J2,I 2),I2,IF(concatenate(C2,E2 )=concatenate(J2,K2),I2,IF(concatenate(C2,E2)=conc atenate(K2,I2),I2,IF(con ca tenate(C2,E2)=concatenate(K2,J2),I2, IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(con catenate(D2,C2)=concatenat e(I2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I 2),I2,IF(concatenate(D2,C2 )=concatenate(J2,K2),I2,IF(concatenate(D2,C2)=conc atenate(K2,I2),I2,IF(con ca tenate(D2,C2)=concatenate(K2,J2),I2, IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(con catenate(D2,E2)=concatenat e(I2,K2),I2,IF(concatenate(D2,E2)=concatenate(J2,I 2),I2,IF(concatenate(D2,E2 )=concatenate(J2,K2),I2,IF(concatenate(D2,E2)=conc atenate(K2,I2),I2,IF(con ca tenate(D2,E2)=concatenate(K2,J2),I2, IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(con catenate(E2,C2)=concatenat e(I2,K2),I2,IF(concatenate(E2,C2)=concatenate(J2,I 2),I2,IF(concatenate(E2,C2 )=concatenate(J2,K2),I2,IF(concatenate(E2,C2)=conc atenate(K2,I2),I2,IF(con ca tenate(E2,C2)=concatenate(K2,J2),I2, IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(con catenate(E2,D2)=concatenat e(I2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I 2),I2,IF(concatenate(E2,D2 )=concatenate(J2,K2),I2,IF(concatenate(E2,D2)=conc atenate(K2,I2),I2,IF(con ca tenate(E2,D2)=concatenate(K2,J2),I2,"")))))))))))) ))))))))... I think that is about it... if this don't confound the brain I don't know what will:) Thank you Bob for your responses "Bob Phillips" wrote: Can you explain why it should be 88, 15 "" as I am afraid the formula makes no sense (to me) -- HTH Bob Phillips "Luke" wrote in message ... I made a mistake and put a mutiplication sign in the formula. The correct formula: =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Not sure how that "*" sign got in there. sorry Regards, Luke "Luke" wrote: I created this function to show you exactly what I need. I know if I plug it in it will return a #VALUE# error. I tried to create it without the OR() function but it is too long for Excel. Is there a more simple way to write this? Sheet1: A B C D E F G H I J K 1 2 2 8 8 8 8 should show 88 3 1 0 5 9 5 1 should show 15 4 8 3 4 5 2 4 should show "" =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Thank you for your time Luke |
See you caught me at another mistake... I should have used the entire senario
from the beginning...Appologies. I need, should the conditions of the formula are true, F2 to show I2, G2 to show J2 and H2 to show K2. I put the results in F G & H respectively: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 I really am sorry for the confusion as I made worse trying to make it easier. Luke "Vasant Nanavati" wrote: Still confused. Shouldn't G3 show 9, then? -- Vasant "Luke" wrote in message ... in the example C2=2 D2=8 E2=8 I2=8 J2="" K2=8 Sorry if they are not lined up. I2 is 8 so concatenate(I2,K2)=concatenate(D2,E2) and therefore G2 should show 8 regards Luke "Vasant Nanavati" wrote: Still isn't right. I2 is "", not 8. -- Vasant "Luke" wrote in message ... Opps again I made an error on this table... It should have read: A B C D E F G H I J K 1 2 2 8 8 8 8 G2 should show 8 3 1 0 5 9 5 1 G3 should show "" 4 8 3 4 5 2 4 G4 should show "" I should have pointed out that in this example I want I2 to show up in G2 but only if any combination of two or more cells in I2 J2 and K2 are equal to any combination of two or more cells in C2 D2 and E2. each (C2,D2,E2) and (I2,J2,K2) has 6 different combinations totaling 36 different senarios. So, if I concatenate cell C2 and D2 they don't equal any concatenated combination of any two given cells in I2,J2 & k2. However if I concatenate(D2,E2) they are the same as Concatenate(I2,K2). I'll elaborate with that way too long formula of which I broke up into 6 segments so it is easir to see: =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2, IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(con catenate(C2,E2)=concatenat e(I2,K2),I2,IF(concatenate(C2,E2)=concatenate(J2,I 2),I2,IF(concatenate(C2,E2 )=concatenate(J2,K2),I2,IF(concatenate(C2,E2)=conc atenate(K2,I2),I2,IF(con ca tenate(C2,E2)=concatenate(K2,J2),I2, IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(con catenate(D2,C2)=concatenat e(I2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I 2),I2,IF(concatenate(D2,C2 )=concatenate(J2,K2),I2,IF(concatenate(D2,C2)=conc atenate(K2,I2),I2,IF(con ca tenate(D2,C2)=concatenate(K2,J2),I2, IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(con catenate(D2,E2)=concatenat e(I2,K2),I2,IF(concatenate(D2,E2)=concatenate(J2,I 2),I2,IF(concatenate(D2,E2 )=concatenate(J2,K2),I2,IF(concatenate(D2,E2)=conc atenate(K2,I2),I2,IF(con ca tenate(D2,E2)=concatenate(K2,J2),I2, IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(con catenate(E2,C2)=concatenat e(I2,K2),I2,IF(concatenate(E2,C2)=concatenate(J2,I 2),I2,IF(concatenate(E2,C2 )=concatenate(J2,K2),I2,IF(concatenate(E2,C2)=conc atenate(K2,I2),I2,IF(con ca tenate(E2,C2)=concatenate(K2,J2),I2, IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(con catenate(E2,D2)=concatenat e(I2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I 2),I2,IF(concatenate(E2,D2 )=concatenate(J2,K2),I2,IF(concatenate(E2,D2)=conc atenate(K2,I2),I2,IF(con ca tenate(E2,D2)=concatenate(K2,J2),I2,"")))))))))))) ))))))))... I think that is about it... if this don't confound the brain I don't know what will:) Thank you Bob for your responses "Bob Phillips" wrote: Can you explain why it should be 88, 15 "" as I am afraid the formula makes no sense (to me) -- HTH Bob Phillips "Luke" wrote in message ... I made a mistake and put a mutiplication sign in the formula. The correct formula: =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Not sure how that "*" sign got in there. sorry Regards, Luke "Luke" wrote: I created this function to show you exactly what I need. I know if I plug it in it will return a #VALUE# error. I tried to create it without the OR() function but it is too long for Excel. Is there a more simple way to write this? Sheet1: A B C D E F G H I J K 1 2 2 8 8 8 8 should show 88 3 1 0 5 9 5 1 should show 15 4 8 3 4 5 2 4 should show "" =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Thank you for your time Luke |
Why does G2 = 8, when J2 = ""?
if the result were to be F2=8 G2="" and H2=8 I would assume you could do something like in F2 put =if(and(--(countif(C2:E2,I2)0)--(countif(C2:E2,J2)0)--(Countif(C2:E2,K2)0)1),--countif(c2:E2,I2)0),I2,"") in G2 put =if(and(--(countif(C2:E2,I2)0)--(countif(C2:E2,J2)0)--(Countif(C2:E2,K2)0)1),--countif(c2:E2,I2)0),J2,"") in H2 put =if(and(--(countif(C2:E2,I2)0)--(countif(C2:E2,J2)0)--(Countif(C2:E2,K2)0)1),--countif(c2:E2,I2)0),k2,"") "Luke" wrote: See you caught me at another mistake... I should have used the entire senario from the beginning...Appologies. I need, should the conditions of the formula are true, F2 to show I2, G2 to show J2 and H2 to show K2. I put the results in F G & H respectively: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 I really am sorry for the confusion as I made worse trying to make it easier. Luke "Vasant Nanavati" wrote: Still confused. Shouldn't G3 show 9, then? -- Vasant "Luke" wrote in message ... in the example C2=2 D2=8 E2=8 I2=8 J2="" K2=8 Sorry if they are not lined up. I2 is 8 so concatenate(I2,K2)=concatenate(D2,E2) and therefore G2 should show 8 regards Luke "Vasant Nanavati" wrote: Still isn't right. I2 is "", not 8. -- Vasant "Luke" wrote in message ... Opps again I made an error on this table... It should have read: A B C D E F G H I J K 1 2 2 8 8 8 8 G2 should show 8 3 1 0 5 9 5 1 G3 should show "" 4 8 3 4 5 2 4 G4 should show "" I should have pointed out that in this example I want I2 to show up in G2 but only if any combination of two or more cells in I2 J2 and K2 are equal to any combination of two or more cells in C2 D2 and E2. each (C2,D2,E2) and (I2,J2,K2) has 6 different combinations totaling 36 different senarios. So, if I concatenate cell C2 and D2 they don't equal any concatenated combination of any two given cells in I2,J2 & k2. However if I concatenate(D2,E2) they are the same as Concatenate(I2,K2). I'll elaborate with that way too long formula of which I broke up into 6 segments so it is easir to see: =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2, IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(con catenate(C2,E2)=concatenat e(I2,K2),I2,IF(concatenate(C2,E2)=concatenate(J2,I 2),I2,IF(concatenate(C2,E2 )=concatenate(J2,K2),I2,IF(concatenate(C2,E2)=conc atenate(K2,I2),I2,IF(con ca tenate(C2,E2)=concatenate(K2,J2),I2, IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(con catenate(D2,C2)=concatenat e(I2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I 2),I2,IF(concatenate(D2,C2 )=concatenate(J2,K2),I2,IF(concatenate(D2,C2)=conc atenate(K2,I2),I2,IF(con ca tenate(D2,C2)=concatenate(K2,J2),I2, IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(con catenate(D2,E2)=concatenat e(I2,K2),I2,IF(concatenate(D2,E2)=concatenate(J2,I 2),I2,IF(concatenate(D2,E2 )=concatenate(J2,K2),I2,IF(concatenate(D2,E2)=conc atenate(K2,I2),I2,IF(con ca tenate(D2,E2)=concatenate(K2,J2),I2, IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(con catenate(E2,C2)=concatenat e(I2,K2),I2,IF(concatenate(E2,C2)=concatenate(J2,I 2),I2,IF(concatenate(E2,C2 )=concatenate(J2,K2),I2,IF(concatenate(E2,C2)=conc atenate(K2,I2),I2,IF(con ca tenate(E2,C2)=concatenate(K2,J2),I2, IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(con catenate(E2,D2)=concatenat e(I2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I 2),I2,IF(concatenate(E2,D2 )=concatenate(J2,K2),I2,IF(concatenate(E2,D2)=conc atenate(K2,I2),I2,IF(con ca tenate(E2,D2)=concatenate(K2,J2),I2,"")))))))))))) ))))))))... I think that is about it... if this don't confound the brain I don't know what will:) Thank you Bob for your responses "Bob Phillips" wrote: Can you explain why it should be 88, 15 "" as I am afraid the formula makes no sense (to me) -- HTH Bob Phillips "Luke" wrote in message ... I made a mistake and put a mutiplication sign in the formula. The correct formula: =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Not sure how that "*" sign got in there. sorry Regards, Luke "Luke" wrote: I created this function to show you exactly what I need. I know if I plug it in it will return a #VALUE# error. I tried to create it without the OR() function but it is too long for Excel. Is there a more simple way to write this? Sheet1: A B C D E F G H I J K 1 2 2 8 8 8 8 should show 88 3 1 0 5 9 5 1 should show 15 4 8 3 4 5 2 4 should show "" =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Thank you for your time Luke |
I meant to add if my assumption that what you want to do is that
if at least two cells from ABC match at least two cells in IJF in any order and I is one copy I to F if at least two cells from ABC match at least two cells in IJF in any order and J is one copy J to G if at least two cells from ABC match at least two cells in IJF in any order and K is one copy K to H after saying this change my equations frorm the last response to: in F2 put =if(and(--(countif(C2:E2,I2)0)--(countif(C2:E2,J2)0)--(Countif(C2:E2,K2)0)1 ,--(countif(I2:K2,C2)0)--(countif(I2:K2,D2)0)--(Countif(I2:K2,E2)0)1, --countif(c2:E2,I2)0),I2,"") in G2 put =if(and(--(countif(C2:E2,I2)0)--(countif(C2:E2,J2)0)--(Countif(C2:E2,K2)0)1),--(countif(I2:K2,C2)0)--(countif(I2:K2,D2)0)--(Countif(I2:K2,E2)0)1,--countif(c2:E2,I2)0),J2,"") in H2 put =if(and(--(countif(C2:E2,I2)0)--(countif(C2:E2,J2)0)--(Countif(C2:E2,K2)0)1),--(countif(I2:K2,C2)0)--(countif(I2:K2,D2)0)--(Countif(I2:K2,E2)0)1,--countif(c2:E2,I2)0),k2,"") "Luke" wrote: See you caught me at another mistake... I should have used the entire senario from the beginning...Appologies. I need, should the conditions of the formula are true, F2 to show I2, G2 to show J2 and H2 to show K2. I put the results in F G & H respectively: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 I really am sorry for the confusion as I made worse trying to make it easier. Luke "Vasant Nanavati" wrote: Still confused. Shouldn't G3 show 9, then? -- Vasant "Luke" wrote in message ... in the example C2=2 D2=8 E2=8 I2=8 J2="" K2=8 Sorry if they are not lined up. I2 is 8 so concatenate(I2,K2)=concatenate(D2,E2) and therefore G2 should show 8 regards Luke "Vasant Nanavati" wrote: Still isn't right. I2 is "", not 8. -- Vasant "Luke" wrote in message ... Opps again I made an error on this table... It should have read: A B C D E F G H I J K 1 2 2 8 8 8 8 G2 should show 8 3 1 0 5 9 5 1 G3 should show "" 4 8 3 4 5 2 4 G4 should show "" I should have pointed out that in this example I want I2 to show up in G2 but only if any combination of two or more cells in I2 J2 and K2 are equal to any combination of two or more cells in C2 D2 and E2. each (C2,D2,E2) and (I2,J2,K2) has 6 different combinations totaling 36 different senarios. So, if I concatenate cell C2 and D2 they don't equal any concatenated combination of any two given cells in I2,J2 & k2. However if I concatenate(D2,E2) they are the same as Concatenate(I2,K2). I'll elaborate with that way too long formula of which I broke up into 6 segments so it is easir to see: =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2, IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(con catenate(C2,E2)=concatenat e(I2,K2),I2,IF(concatenate(C2,E2)=concatenate(J2,I 2),I2,IF(concatenate(C2,E2 )=concatenate(J2,K2),I2,IF(concatenate(C2,E2)=conc atenate(K2,I2),I2,IF(con ca tenate(C2,E2)=concatenate(K2,J2),I2, IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(con catenate(D2,C2)=concatenat e(I2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I 2),I2,IF(concatenate(D2,C2 )=concatenate(J2,K2),I2,IF(concatenate(D2,C2)=conc atenate(K2,I2),I2,IF(con ca tenate(D2,C2)=concatenate(K2,J2),I2, IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(con catenate(D2,E2)=concatenat e(I2,K2),I2,IF(concatenate(D2,E2)=concatenate(J2,I 2),I2,IF(concatenate(D2,E2 )=concatenate(J2,K2),I2,IF(concatenate(D2,E2)=conc atenate(K2,I2),I2,IF(con ca tenate(D2,E2)=concatenate(K2,J2),I2, IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(con catenate(E2,C2)=concatenat e(I2,K2),I2,IF(concatenate(E2,C2)=concatenate(J2,I 2),I2,IF(concatenate(E2,C2 )=concatenate(J2,K2),I2,IF(concatenate(E2,C2)=conc atenate(K2,I2),I2,IF(con ca tenate(E2,C2)=concatenate(K2,J2),I2, IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(con catenate(E2,D2)=concatenat e(I2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I 2),I2,IF(concatenate(E2,D2 )=concatenate(J2,K2),I2,IF(concatenate(E2,D2)=conc atenate(K2,I2),I2,IF(con ca tenate(E2,D2)=concatenate(K2,J2),I2,"")))))))))))) ))))))))... I think that is about it... if this don't confound the brain I don't know what will:) Thank you Bob for your responses "Bob Phillips" wrote: Can you explain why it should be 88, 15 "" as I am afraid the formula makes no sense (to me) -- HTH Bob Phillips "Luke" wrote in message ... I made a mistake and put a mutiplication sign in the formula. The correct formula: =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Not sure how that "*" sign got in there. sorry Regards, Luke "Luke" wrote: I created this function to show you exactly what I need. I know if I plug it in it will return a #VALUE# error. I tried to create it without the OR() function but it is too long for Excel. Is there a more simple way to write this? Sheet1: A B C D E F G H I J K 1 2 2 8 8 8 8 should show 88 3 1 0 5 9 5 1 should show 15 4 8 3 4 5 2 4 should show "" =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Thank you for your time Luke |
Hi bj,
I made another thread on 4/30/05 a short time after this post because I kept making mistakes. Please look at it and know that I tried this formula that you made and it is probably good but it is based on errors that I inadvertantly made when I posted this one. read those post /threads as they will give better insight as to what I am really trying to do. Thanks for your efforts and I will look for responses there. Luke "bj" wrote: I meant to add if my assumption that what you want to do is that if at least two cells from ABC match at least two cells in IJF in any order and I is one copy I to F if at least two cells from ABC match at least two cells in IJF in any order and J is one copy J to G if at least two cells from ABC match at least two cells in IJF in any order and K is one copy K to H after saying this change my equations frorm the last response to: in F2 put =if(and(--(countif(C2:E2,I2)0)--(countif(C2:E2,J2)0)--(Countif(C2:E2,K2)0)1 ,--(countif(I2:K2,C2)0)--(countif(I2:K2,D2)0)--(Countif(I2:K2,E2)0)1, --countif(c2:E2,I2)0),I2,"") in G2 put =if(and(--(countif(C2:E2,I2)0)--(countif(C2:E2,J2)0)--(Countif(C2:E2,K2)0)1),--(countif(I2:K2,C2)0)--(countif(I2:K2,D2)0)--(Countif(I2:K2,E2)0)1,--countif(c2:E2,I2)0),J2,"") in H2 put =if(and(--(countif(C2:E2,I2)0)--(countif(C2:E2,J2)0)--(Countif(C2:E2,K2)0)1),--(countif(I2:K2,C2)0)--(countif(I2:K2,D2)0)--(Countif(I2:K2,E2)0)1,--countif(c2:E2,I2)0),k2,"") "Luke" wrote: See you caught me at another mistake... I should have used the entire senario from the beginning...Appologies. I need, should the conditions of the formula are true, F2 to show I2, G2 to show J2 and H2 to show K2. I put the results in F G & H respectively: A B C D E F G H I J K 1 2 2 8 8 8 8 8 8 3 1 0 5 5 1 9 5 1 4 8 3 4 5 2 4 5 8 6 7 6 8 6 2 8 I really am sorry for the confusion as I made worse trying to make it easier. Luke "Vasant Nanavati" wrote: Still confused. Shouldn't G3 show 9, then? -- Vasant "Luke" wrote in message ... in the example C2=2 D2=8 E2=8 I2=8 J2="" K2=8 Sorry if they are not lined up. I2 is 8 so concatenate(I2,K2)=concatenate(D2,E2) and therefore G2 should show 8 regards Luke "Vasant Nanavati" wrote: Still isn't right. I2 is "", not 8. -- Vasant "Luke" wrote in message ... Opps again I made an error on this table... It should have read: A B C D E F G H I J K 1 2 2 8 8 8 8 G2 should show 8 3 1 0 5 9 5 1 G3 should show "" 4 8 3 4 5 2 4 G4 should show "" I should have pointed out that in this example I want I2 to show up in G2 but only if any combination of two or more cells in I2 J2 and K2 are equal to any combination of two or more cells in C2 D2 and E2. each (C2,D2,E2) and (I2,J2,K2) has 6 different combinations totaling 36 different senarios. So, if I concatenate cell C2 and D2 they don't equal any concatenated combination of any two given cells in I2,J2 & k2. However if I concatenate(D2,E2) they are the same as Concatenate(I2,K2). I'll elaborate with that way too long formula of which I broke up into 6 segments so it is easir to see: =IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D 2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc atenate(C2,D2)=concatenate(K2,J2),I2, IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(con catenate(C2,E2)=concatenat e(I2,K2),I2,IF(concatenate(C2,E2)=concatenate(J2,I 2),I2,IF(concatenate(C2,E2 )=concatenate(J2,K2),I2,IF(concatenate(C2,E2)=conc atenate(K2,I2),I2,IF(con ca tenate(C2,E2)=concatenate(K2,J2),I2, IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(con catenate(D2,C2)=concatenat e(I2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I 2),I2,IF(concatenate(D2,C2 )=concatenate(J2,K2),I2,IF(concatenate(D2,C2)=conc atenate(K2,I2),I2,IF(con ca tenate(D2,C2)=concatenate(K2,J2),I2, IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(con catenate(D2,E2)=concatenat e(I2,K2),I2,IF(concatenate(D2,E2)=concatenate(J2,I 2),I2,IF(concatenate(D2,E2 )=concatenate(J2,K2),I2,IF(concatenate(D2,E2)=conc atenate(K2,I2),I2,IF(con ca tenate(D2,E2)=concatenate(K2,J2),I2, IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(con catenate(E2,C2)=concatenat e(I2,K2),I2,IF(concatenate(E2,C2)=concatenate(J2,I 2),I2,IF(concatenate(E2,C2 )=concatenate(J2,K2),I2,IF(concatenate(E2,C2)=conc atenate(K2,I2),I2,IF(con ca tenate(E2,C2)=concatenate(K2,J2),I2, IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(con catenate(E2,D2)=concatenat e(I2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I 2),I2,IF(concatenate(E2,D2 )=concatenate(J2,K2),I2,IF(concatenate(E2,D2)=conc atenate(K2,I2),I2,IF(con ca tenate(E2,D2)=concatenate(K2,J2),I2,"")))))))))))) ))))))))... I think that is about it... if this don't confound the brain I don't know what will:) Thank you Bob for your responses "Bob Phillips" wrote: Can you explain why it should be 88, 15 "" as I am afraid the formula makes no sense (to me) -- HTH Bob Phillips "Luke" wrote in message ... I made a mistake and put a mutiplication sign in the formula. The correct formula: =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Not sure how that "*" sign got in there. sorry Regards, Luke "Luke" wrote: I created this function to show you exactly what I need. I know if I plug it in it will return a #VALUE# error. I tried to create it without the OR() function but it is too long for Excel. Is there a more simple way to write this? Sheet1: A B C D E F G H I J K 1 2 2 8 8 8 8 should show 88 3 1 0 5 9 5 1 should show 15 4 8 3 4 5 2 4 should show "" =IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE( D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN ATE(K2,J2)),I2,"") Thank you for your time Luke |
| All times are GMT +1. The time now is 06:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com