Solved: CREATE 2X1 MATRIX IN EXCEL AND PULL FROM THIS

[IrishCharm]

Hi,

Basically i have data in column A and data in column B. I want in column c to have all data from A and B but only distinct amounts, i.e. no overlaps. I knew of a way to do this within VB matrix but not sure how to start about this.

Thanks for any help!!

I have attached the document. Basically there is a long way about going about it but i know in VB it can create a matrix i,j where i is the list of values in A and j is the list of values in B and then we can return distinct cases of i,j into column C.

[mdmackillop]

:dunno

[tstav]

but not sure how to start about this

Since you're asking for a hint and not the final code, let me show you one of several ways.

Copy all values from col A to col C.
Copy all values from col B to col C right below the values you just pasted from col A.
Now you have all values in col C.
Looping from the bottom of col C to the top, check the value of each cell with the worksheet.countif (col C,cell.value).
If the result is >1 delete that cell and move to the next.
At the end you will be left with the unique cell values.

P.S. If that is not what you asked for, maybe you should rephrase your post

[IrishCharm]

Since you're asking for a hint and not the final code, let me show you one of several ways.

Copy all values from col A to col C.
Copy all values from col B to col C right below the values you just pasted from col A.
Now you have all values in col C.
Looping from the bottom of col C to the top, check the value of each cell with the worksheet.countif (col C,cell.value).
If the result is >1 delete that cell and move to the next.
At the end you will be left with the unique cell values.

P.S. If that is not what you asked for, maybe you should rephrase your post


Hi, I have attached the document and a more detailed explanation. Cheers

[tstav]

Would this be a valid value for col C?
719438-697,719438-697 (I picked the first value from each col A, colB)

I mean string comma string?

[IrishCharm]

Would this be a valid value for col C?
719438-697,719438-697 (I picked the first value from each col A, colB)

I mean string comma string?

Hi,

I am trying to automate this in a better format than what i currently do. I copy A into D and copy B into D underneath it. I sort col D and do a lookup in column C, ie if D1 = d2,"", d2 and i return the unique values this way.
But i know that within the VB code you can set up a matrix to gather all values in column A, gather all values in col B and then return only the uniquue of each in Col C.

Cheers

[rory]

Like this?
Sub GetUniques()
Dim lngMaxRow As Long, lngRow As Long, lngCol As Long
Dim objColl As New Collection
lngMaxRow = Application.Max(Cells(Rows.Count, 1).End(xlUp).Row, Cells(Rows.Count, 1).End(xlUp).Row)
On Error Resume Next
For lngRow = 2 To lngMaxRow
For lngCol = 1 To 2
With Cells(lngRow, lngCol)
If Len(.Value) > 0 Then
objColl.Add .Value, .Value
End If
End With
Next lngCol
Next lngRow
For lngRow = 1 To objColl.Count
Cells(lngRow + 1, "C").Value = objColl(lngRow)
Next lngRow
End Sub

[IrishCharm]

EXCELLENT, you're a star thanks so much for that - will save me so much hardship of copy and paste!!!!!

Have a nice Day!!
:hi:

[IrishCharm]

Hi Rory,

How would i amend this to, say, only return values that are in column A but not in column B? Tried changing it to do this but it does not seem to work for me.

Cheers

Sarah

[rory]

Hi Sarah,
You could make it more generic so that it just runs off the selected data (still outputs to column C):
Sub GetUniques()
Dim rngCell As Range
Dim lngRow As Long
Dim objColl As New Collection
On Error Resume Next
For Each rngCell In Selection
With rngCell
If Len(.Value) > 0 Then
objColl.Add .Value, .Value
End If
End With
Next rngCell
For lngRow = 1 To objColl.Count
Cells(lngRow + 1, "C").Value = objColl(lngRow)
Next lngRow
End Sub

[IrishCharm]

Hi Rory,

I made a few amendments to the first code you sent on and I was able to chage it for different columns. I'm only intermediate VB and have been trying to work out the second code you gave me but am unable. If i paste it directly in and highlight a column it just returns all values. Could you please send me on a worked example, say, return only values in one column which are not in another and i can work backwards from there to amend it for different versions?

Kind regards,

sarah

[rory]

Sarah,
Sorry - the reason you couldn't figure out the code is because I completely misread your question! I will post a revised version shortly (work permitting).

[rory]

How's this?
Sub GetUniquesAversusB()
Dim rngCell As Range, rngData As Range, rngCheckList As Range, rngOut As Range
Dim lngRow As Long
Dim objColl As New Collection
' Select data list
Set rngData = Application.InputBox(Prompt:="Select data list to be checked", _
Title:="List data", Type:=8)
If rngData Is Nothing Then
' nothing selected
Exit Sub
Else
' only want used rows
Set rngData = Intersect(rngData, rngData.Parent.UsedRange)
End If

' get list to check against
Set rngCheckList = Application.InputBox(Prompt:="Select data to check against", _
Title:="Check list", Type:=8)
If rngCheckList Is Nothing Then
' nothing selected
Exit Sub
Else
' only want used rows
Set rngCheckList = Intersect(rngCheckList, rngCheckList.Parent.UsedRange)
End If

' Get output range
Set rngOut = Application.InputBox(Prompt:="Select output range", _
Title:="Destination", Type:=8)
If rngOut Is Nothing Then
Exit Sub
Else
' only want one cell
Set rngOut = rngOut.Cells(1)
End If

' need this because attempting to add duplicate entries to collection will error
On Error Resume Next
For Each rngCell In rngData
With rngCell
If Len(.Value) > 0 Then
' check there is no match in check list
If Application.CountIf(rngCheckList, .Value) = 0 Then
' add to collection - ensures unique items only
objColl.Add .Value, .Value
End If
End If
End With
Next rngCell
For lngRow = 1 To objColl.Count
rngOut.Offset(lngRow - 1).Value = objColl(lngRow)
Next lngRow
End Sub

[IrishCharm]

You, my friend are a genius - this works a treat. The comments are great also thanks - i should be able to work backwards and change to fit my code. Comments make it much easier to understand what is going on.

Thanks again for all your help.

Sarah