| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to show whether 3 cells within a row match 3 cells in another
Novice here...
How do I show or highlight, within a single worksheet, rows of cells that have matching data with other rows in another part of the worksheet. For example: the worksheet has about 10,000 rows, and 11 columns. I want to the formula to look through the entire worksheet and highlight for me any single row which "matches" any other single row. Whether or not it matches would be based on whether the data in columns B, E, and J for that row match. Column B is a number, Column E is a date, and Column J is another number. 025847 10/27/2008 012 025847 10/27/2008 013 025843 10/27/2008 012 025847 10/27/2008 012 025847 10/27/2008 012 For example, rows 1, 4, and 5 from the above example, I want those to show up as matches. Is this feasible? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to show whether 3 cells within a row match 3 cells in another
simplest is usually the quickest...or I've just got a dumb way of doing
things. I'd create an extra column which equalled the text value of the cells in question ie ="b"&"E"&"J" which will give you a unique value. Then click on pivottable and arrange it so your unique value is the first column and any number is the datatable, it'll group any duplicates for you ta DOug "Swish7" wrote: Novice here... How do I show or highlight, within a single worksheet, rows of cells that have matching data with other rows in another part of the worksheet. For example: the worksheet has about 10,000 rows, and 11 columns. I want to the formula to look through the entire worksheet and highlight for me any single row which "matches" any other single row. Whether or not it matches would be based on whether the data in columns B, E, and J for that row match. Column B is a number, Column E is a date, and Column J is another number. 025847 10/27/2008 012 025847 10/27/2008 013 025843 10/27/2008 012 025847 10/27/2008 012 025847 10/27/2008 012 For example, rows 1, 4, and 5 from the above example, I want those to show up as matches. Is this feasible? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I would like to avoid pivot tables if possible
I was hoping this could be done with conditional formatting..
"DReid" wrote: simplest is usually the quickest...or I've just got a dumb way of doing things. I'd create an extra column which equalled the text value of the cells in question ie ="b"&"E"&"J" which will give you a unique value. Then click on pivottable and arrange it so your unique value is the first column and any number is the datatable, it'll group any duplicates for you ta DOug "Swish7" wrote: Novice here... How do I show or highlight, within a single worksheet, rows of cells that have matching data with other rows in another part of the worksheet. For example: the worksheet has about 10,000 rows, and 11 columns. I want to the formula to look through the entire worksheet and highlight for me any single row which "matches" any other single row. Whether or not it matches would be based on whether the data in columns B, E, and J for that row match. Column B is a number, Column E is a date, and Column J is another number. 025847 10/27/2008 012 025847 10/27/2008 013 025843 10/27/2008 012 025847 10/27/2008 012 025847 10/27/2008 012 For example, rows 1, 4, and 5 from the above example, I want those to show up as matches. Is this feasible? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I would like to avoid pivot tables if possible
Another reason this can't work for me is that I would have to change the
formats of all the relevant cells from text or date to number in order for them to be summable. Changing the format would change the values in those cells, and render them useless. "Swish7" wrote: I was hoping this could be done with conditional formatting.. "DReid" wrote: simplest is usually the quickest...or I've just got a dumb way of doing things. I'd create an extra column which equalled the text value of the cells in question ie ="b"&"E"&"J" which will give you a unique value. Then click on pivottable and arrange it so your unique value is the first column and any number is the datatable, it'll group any duplicates for you ta DOug "Swish7" wrote: Novice here... How do I show or highlight, within a single worksheet, rows of cells that have matching data with other rows in another part of the worksheet. For example: the worksheet has about 10,000 rows, and 11 columns. I want to the formula to look through the entire worksheet and highlight for me any single row which "matches" any other single row. Whether or not it matches would be based on whether the data in columns B, E, and J for that row match. Column B is a number, Column E is a date, and Column J is another number. 025847 10/27/2008 012 025847 10/27/2008 013 025843 10/27/2008 012 025847 10/27/2008 012 025847 10/27/2008 012 For example, rows 1, 4, and 5 from the above example, I want those to show up as matches. Is this feasible? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I would like to avoid pivot tables if possible
try this formula in your conditional formatting: =AND(COUNTIF($A:$A,A1)1,
COUNTIF($B:$B,B1)1,COUNTIF($C:$C,C1)1) Swish7 wrote: Another reason this can't work for me is that I would have to change the formats of all the relevant cells from text or date to number in order for them to be summable. Changing the format would change the values in those cells, and render them useless. I was hoping this could be done with conditional formatting.. [quoted text clipped - 30 lines] Is this feasible? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200806/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
| Cells won't change font color or show hi-lighted cells in document | Excel Discussion (Misc queries) | |||
| Match cells with cells on another workbook | Excel Worksheet Functions | |||
| Want to show/hide cells depending on other cells results | Excel Discussion (Misc queries) | |||
| Query counting cells in a row that exactly match cells in another | Excel Discussion (Misc queries) | |||
Linear Mode
