Extract rows based upon unique data in first column of an array

[RJC40]

Hi,

I am trying to right a sub that opens a csv file adds to an array, then the data of the first column of the array to find unique values compared to the database in a worksheet. Values that come back TRUE will have the rest of the data in the array row added to the bottom of the dataset in the worksheet.
I am struggling with the final bit, the code is searching all values and i cannot see how to extract the rows I wish.
Any thoughts would be great!

Note: GetFile() is a Function

Option Explicit
Sub LoadToArray()
Application.ScreenUpdating = False


Dim file As String: file = GetFile()
Dim wbCSV As Workbook
Dim Data As Variant
Dim rg As Range


Set wbCSV = Workbooks.Open(filename:=file, ReadOnly:=True)
Set rg = wbCSV.Worksheets(1).Range("A1").CurrentRegion

    Data = rg.Value

    wbCSV.Close SaveChanges:=False


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim ws As Worksheet
Dim activeSheetData As Range
Dim cellValue As Variant
Dim uniqueValues As Collection
Dim lastRow As Long
Dim isUnique As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set ws = ActiveSheet
Set activeSheetData = ActiveSheet.Range("A1:A" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row)
Set uniqueValues = New Collection
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row + 1

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''







For Each cellValue In Data
       
        
        If isUnique Then
        
        ws.Rows(lastRow) = cellValue
      
        Else
            
        End If
        
        
Next cellValue

      
End Sub

y row added to the bottom of the data base.

 

[DanteAmor]

Hi RJC40

You could explain with about 6 examples, which must have different cases, that is, try to give several examples, trying to represent what you need.
What do you have in the CSV file.
What do you have in the active sheet database.
Explain what you want to do with each record, that is, what data you want to extract and where you want to put it.
Consider that you must put how the data is before the macro and how the data will look after the macro.

Forget about the macro and explain in your words what you want as a result.

Note XL2BB:
Would help greatly if you could give us the sample data in a form that we can copy to test with.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[RJC40]

Hi DanteAmor,

Lets not worry about the csv, this works as expected.
The data loads to the array as expected - it is the final step that requires work.


For Example:

I have an array, lets say 3 columns, 12 rows, A-L in column 1, addresses in 2, and telephone numbers in 3.

I have also have a worksheet with similar, however, this currently 3 columns, 10 rows A-J in column 1, addresses in 2, and telephone numbers in 3.
_____________________________________________________________________________________________________________________________________________________________________________

I would like search for the unique values in column 1 of the array checking against the data in column 1 in the worksheet (in the example above, that would be 'K' and 'L' would be the only results returned TRUE).

Once the unique data has been identified in the array, add the whole row of data in which the value is located from the array to the bottom of the dataset in the worksheet, ie 'K, Bob, 0225549', and so on.

The data itself is not important, it will always be changing, however, I wish to ensure that I am only adding rows that have a unique value in column 1.

I hope this helps