Comparing characters in one cell to multiple cells
I'm trying to create a method that will return a value from the
differences between cells. Example data A B C D E F G H Row 1: BBB BBS BSB BSS SBB SSS BBB Result Row 2: BSB BSB BBS SSS Result The left column is the reference and the others are compared against this. There are at most six columns to compare against (B through G). The cells in any column may be blank, I'd like to ignore these. The worksheet will have over 5000 lines with data. For row 1: Row 2 A to B: 1 difference 0 A to C: 1 2 A to D: 2 3 A to E: 1 A to F: 3 A to G: 0 So I'd like the result to be 8 for row 1 and 5 for row 2 with one result per row. I've thought of something like this: =SUM(IF(MID(a1,1,1)<MID(b1,1,1), 1,0)*1,IF(MID(a1,2,1)<MID(b1,2,1),1,0)*1,IF(MID(a 1,3,1)<MID(b1,3,1), 1,0)*1) but the resulting function for six columns would be huge. I hope there's something more managable that can be done. |
Comparing characters in one cell to multiple cells
=3-((MID($A$1,1,1)=MID(B1,1,1))+(MID($A$1,2,1)=MID(B1 ,2,1))+(MID($A$1,3,1)=MID(B1,3,1)))
copy across "Quirthanon" wrote: I'm trying to create a method that will return a value from the differences between cells. Example data A B C D E F G H Row 1: BBB BBS BSB BSS SBB SSS BBB Result Row 2: BSB BSB BBS SSS Result The left column is the reference and the others are compared against this. There are at most six columns to compare against (B through G). The cells in any column may be blank, I'd like to ignore these. The worksheet will have over 5000 lines with data. For row 1: Row 2 A to B: 1 difference 0 A to C: 1 2 A to D: 2 3 A to E: 1 A to F: 3 A to G: 0 So I'd like the result to be 8 for row 1 and 5 for row 2 with one result per row. I've thought of something like this: =SUM(IF(MID(a1,1,1)<MID(b1,1,1), 1,0)*1,IF(MID(a1,2,1)<MID(b1,2,1),1,0)*1,IF(MID(a 1,3,1)<MID(b1,3,1), 1,0)*1) but the resulting function for six columns would be huge. I hope there's something more managable that can be done. |
All times are GMT +1. The time now is 01:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com