Array A -> Array B with unique values

[gamerosko]

Hi there,
I get stuck at the problem of having Array A (lets say Col A is month in format MM ; and Col B is year in format YYYY) with multiple records and some of them are same (eg. row 2 A,B Col = 05,2011 and row 5 A,B Col = 05,2011).

Is there a way that I can get by some formula Array B (lets say Col C, D) with unique values = shortened array ?

Thx for help, gamerosko.

 

[kpark91]

What version of excel do you use?

 

[gamerosko]

What version of excel do you use?

sry.. Ex. 2007

 

[kpark91]

Just copy your array (col.A and B) to col C & D to make the same array first.
Then select the array you want to get unique values in. In your case, it would be column C & D array.

Then go to Data tab -> Remove Duplicates -> Check if you have your headers selected -> Make sure your column C and D are checked -> OK

 

[gamerosko]

Just copy your array (col.A and B) to col C & D to make the same array first.
Then select the array you want to get unique values in. In your case, it would be column C & D array.

Then go to Data tab -> Remove Duplicates -> Check if you have your headers selected -> Make sure your column C and D are checked -> OK

Thx, I know about that functionality, unfortunately the problem is, that the data content in Array A is changing every day and the file is opened by many users.
So what I need in Col C,D is some formula that automatically calculates Array A and transfers to Array B with unique values, everytime something is added/changed in Array A.

 

[Peter_SSs]

Perhaps the 'remove duplicates' process, or 'Advanced Filter' as I've used could be performed by a macro that is triggered by any change in columns A or B?

Test this in a copy of your workbook. It assumes headings in A1:B1.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try making changes in the sheet.


<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Columns("A:B")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        Columns("C:D").ClearContents<br>        Intersect(ActiveSheet.UsedRange, Columns("A:B")) _<br>            .AdvancedFilter Action:=xlFilterCopy, _<br>            CopyToRange:=Range("C1"), Unique:=<SPAN style="color:#00007F">True</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[gamerosko]

it looks great.. sorry to bother, wanted to try it out, but I made the example other than I have my values, in order to simplify the problem.

I have Array A with changing values on Sheet named "BSP-Data" in Columns B,C with row#1 headers.
The copied Array B should be on another Sheet named "USER" Columns J,K.

I hope it goes only for some minor changes and it gives you no more work.. sry.

 

[Peter_SSs]

I hope it goes only for some minor changes and it gives you no more work.

Yes, pretty easy changes. Try this version. The code should be placed in the 'BSP-Data' sheet module.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Columns("B:C")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Sheets("USER").Columns("J:K")<br>            .ClearContents<br>            Intersect(ActiveSheet.UsedRange, Columns("B:C")) _<br>                .AdvancedFilter Action:=xlFilterCopy, _<br>                CopyToRange:=.Cells(1, 1), Unique:=<SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[gamerosko]

cool.. it works very well. I am just wondering about a minor upgrade.

Is it possible that it also automatically sorts out those values with a sort logic: #1 by Col K (YYYY) from A to Z ; then #2 by Col J (MM) Col J from A to Z ?
This makes sorted Array having values sorted from oldest at top and newest at bottom..

 

[Peter_SSs]

Add the blue bit where shown

    Intersect(ActiveSheet.UsedRange, Columns("B:C")) _
        .AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=.Cells(1, 1), Unique:=True
    .Sort Key1:=.Cells(2, 2), Order1:=xlAscending, Key2:=.Cells(2, 1), _
        Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
End With