• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Move data after conditional formatting and identification of duplicates

V

Victoria7

New Member
I used conditional formatting to identify duplicate data when comparing 4 columns. Now that I have the duplicates highlighted I want to be able to automatically align the data in my columns so that it is side by side. For example:


Column A | Column B | Column C | Column D

Smith | 587 | Smith | 587

Jones | 662 | Smith | 420


I would like to automatically have my data adjust down so that when the data in Column A & B doesn't match column C & D it somehow moves "Jones 662" down one row so it is no longer side by side with "Smith 420". I am working with a huge spreadsheet and while the conditional formatting helped me flag the accounts that match, I still have to manually align the unmatched entries. Any help would be much appreciated!
 
Hi Victoria ,


Do you mean that you want all matched entries to come together ?


For example , you have mentioned that "Jones" and "662" ( without the quotes ) would move one row down ; but what if the data were as follows :

[pre]
Code: 
Smith      587     Smith     587
Jones      662     Smith     420
Peter      566     Peter     566
In this case , moving "Jones" and "662" down by one row , would mean that the matched entries "Peter" and "566" would get mis-aligned from their counterparts in columns C and D.


Do you want that the output should be as follows ?

Smith      587     Smith     587
Smith     420
Jones      662
Peter      566     Peter     566
[/pre]
If you can use a helper column , then you can have a formula in that column ( say column E ) , =AND(A2=C2,B2=D2) assuming your data starts from row 2 ; copy this formula down ; now sort columns A through E on column E.


Narayan
 
Hi Narayan,


Yes, I want all LIKE entries to be side by side. Your example is exactly what I want. I manually go through the spreadsheet right now and insert rows to split the entries that do not have a match. 'Smith 420' as in your example would have no match to the left and 'Jones 662' would have no match to the right. Then I might have 50 rows that match perfectly and then more mismatches. I'm trying to find an easy way to get those rows with matched entries to align without manually inserting blank rows and adjusting blocks of cells manually.


I tried putting in the formula you suggested in Column E. My data does start in row 2 and I have columns A-D. I put the formula in column E but for every row item the formula result is FALSE therefore when I sort by E no data moves.


Thanks!

Victoria
 
Hi Victoria ,


The formula should give the correct result , unless your data is organized differently. Can you confirm that the data is exactly the way you have posted initially ?


Column A has text entries , column C has text entries. These two should match if the entry is to be considered a match.


Column B has numeric values , column D has numeric values. These two should also match if the entry is to be considered a match.


Thus if in row 2 , A2 has the text "Smith" , without the quotes , B2 has the numeric value 587 , C2 has the text "Smith" , and D2 has the numeric value 587 , then the formula =AND(A2=C2,B2=D2) should return a TRUE result.


Can you confirm ?


Of course , the above formula by itself , will not insert the blank rows wherever there is a mismatch , and shift the mis-matched entries to two separate rows.


The formula will only identify the matched entries.


Narayan
 
Hi Narayan,


I figured out the problem. The formula was returning FALSE because the data in each of my columns was stored as text (from the source) instead of numeric. Once I formatted the cells to numeric the formula returned a value of TRUE.


Thank you very much for your help. Perhaps someone else will weigh in on how I can realign my columns. This formula works very well but of course as soon as I find my first mis-matched row then I need to manually adjust the blocks of data to continue to have the formula return an apppropriate result.


Thanks!

Victoria
 
Hi Victoria ,


What you are looking for can be done using VBA ; is this acceptable to you ?


I just though of another case as follows :

[pre]
Code: 
Smith     587     Jones     662
Peter     576     Dave      576
Jones     662     Wills     420
[/pre]
Can such a situation happen , where the entry "Jones" and "662" in columns A and B appears in columns C and D elsewhere , and not in the same row ?


Narayan
 
Narayan,


When I receive my data from my two different sources I sort each source independently before placing them side by side on my spreadsheet. I usually use alpha so therefore the names and numbers are matched (i.e. Jones 662 regarding your question above).


Next I identify the matches. Using conditional formatting seems to be the easiest so far.


Then, when I have an entry in either column that does not 'match' I manually adjust the blocks of data (drag and drop) and insert rows where needed until all data is aligned either with their respective match or blank cells if no match is found. It would be fantastic if a VBA could help me with this portion. Unfortunately I don't know how to write the code and wouldn't know how to tell the code to adjust the data (i.e., move down, insert row, etc.) when a match isn't found.


Thanks,

Victoria
 
Hi Victoria ,


Can you download the file from here ?


http://speedy.sh/MNNSK/Victoria-Example.xlsm


The sample data is in the range Q5:T7 on Sheet 2 ; the output range starts from G17 on Sheet3.


The macro to be run is named Realign_Matches. Click on the Developer button , click on Macros , and run the macro.


Before running the macro on your data , you will make the required changes :


1. Define a named range called Data_Range , and refer it to your data range


2. Define a named range called Start_cell , and refer it to the first cell of your output range.


Remember to keep a backup copy of your workbook before you do anything , since changes made to data through a macro cannot be undone using the Excel worksheet Undo feature.


Narayan
 
Hi Narayan,


First, thank you VERY much for your help with this. I am finding I have much to learn about Excel.


I did download the file and have tried to utilize it. I'm not sure what I am doing wrong but I keep receiving a VB error code 400.


I believe I have both ranges defined appropriatly. I was not sure if I needed to have my data within the example spreadsheet or just keep the example spreadsheet open when I was trying to run the macro on my data within my own spreadsheet. I tried both ways; keeping the example spreadsheet open and running the macro and also tried putting the data within your spreadsheet. Neither worked.


I also tried it with applying the duplicate conditional formatting to my data first and also without.


Any idea what I am doing wrong?


Thank you,

Victoria
 
Hi Victoria ,


Let us take it step by step.


1. Download the example workbook ; open it , click on Developer , Macros , Run ; see if you get an error.


2. If you do not get an error , then open your own workbook , which has your data in it ; click on Formulas , Name Manager , New , and define the two named ranges Data_Range and Start_cell. Click on Developer , Visual Basic ; when the VB Editor comes up , click on the ThisWorkBook section ( of the example workbook ) in the left panel , copy the entire macro code to the ThisWorkBook section of your workbook. Close the example workbook , and run the macro in your workbook ; see if you get an error.


Please note that before you run the macro in your workbook , you need to make the required changes to the following 2 statements :


Sheets("Sheet2").Activate


Sheet2 is the sheet which has the data , the source worksheet.


Sheets("Sheet3").Activate


Sheet3 is the sheet which will take the realigned data , the destination worksheet.


Replace the references to Sheet2 and Sheet3 with the names of the actual worksheets in your workbook.


Can you do the above and indicate in which step you get an error ?


Narayan
 
Hi Narayan,


I re-downloaded the example workbook and that eliminated the error. All is now working well.


Thank you!

Victoria
 
You must log in or register to reply here.
Back
Top