Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to compare 6 columns of text data
I have six databases which have the same measure in them.Now I have put all
the 6 databases measures in 6 column side by side.I want to match the six columns so that the output is in that form where all the measures that are not matching exactly to any of the six databses are highlighted or False sttement comes up.Here is the example data Databse1 Database 2 Database 3 Database 4................. DollarSales Dollar Sales Dollar Sales $ Sales Dollar Share Dollar Share Dollar Share Unit Sales and so on for around 500 rows.What I want is that all the databases should have the same naming convention....Dollar should be Dollar and not $.Moreover each column data is not alligned with the other column.So I also want the data to be aligned first for all columns and then compare all the six databases and find oth that is there any changes..... Help from anyone will be really very much appreciated as this work is really eating up a lot of my tiime as I am doin it manually... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to compare 6 columns of text data
You can expand this formula pretty easily:
=ISNUMBER(MATCH(1,(A2=Othersheet!$A$1:$A$100)*(B2= Othersheet!$B$1:$B$100),0)) Just keep adding *(C2=othersheet!$c$1:$c$100)... for a total of six columns. This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. Deeptiman wrote: I have six databases which have the same measure in them.Now I have put all the 6 databases measures in 6 column side by side.I want to match the six columns so that the output is in that form where all the measures that are not matching exactly to any of the six databses are highlighted or False sttement comes up.Here is the example data Databse1 Database 2 Database 3 Database 4................. DollarSales Dollar Sales Dollar Sales $ Sales Dollar Share Dollar Share Dollar Share Unit Sales and so on for around 500 rows.What I want is that all the databases should have the same naming convention....Dollar should be Dollar and not $.Moreover each column data is not alligned with the other column.So I also want the data to be aligned first for all columns and then compare all the six databases and find oth that is there any changes..... Help from anyone will be really very much appreciated as this work is really eating up a lot of my tiime as I am doin it manually... -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to compare 6 columns of text data
You can apply autofilters to the columns and carefully note the range
of values that you can see in the pull-down list for each filter - you might see "Dollar xxx", " Dollar xxx", " Dollar xxx", so this will give you some idea of what you have to change. You can then highlight all the cells in your six columns and use Find & Replace (CTRL_H) repeatedly to ensure you have the same naming convention - shouldn't take too long. For example: Find: " D" (space space D) Replace with: "D" Find: " D" (space D) Replace with: "D" Find: " U" Replace with: "U" Find: "$" Replace with: "Dollar " (Dollar space Find: " " (2 spaces) Replace with: " " (single space) Obviously, you don't include the quotes above when you type the entries in. You can then use the filters again to see if you have missed any different formats. Hope this helps. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show text data in excel pivot table | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
How do I compare 2 sets of data and highlight differences? | Excel Worksheet Functions | |||
How to convert columns of data to one column of text | Excel Worksheet Functions |