Compare Two Columns and return text

**Wallace_Gren** · 06-26-2012, 06:42 AM

I am new to this so hopefully this makes sense. Due to large complex spreadsheet and calculations switched off looking for vba code if possible to populate column c.

Column A Column B Column C (Answer)

ProductC MachineA ProductC
ProductC MachineA ProductC
ProductC MachineC Mixed
ProductA MachineC Mixed
ProductB Machine C Mixed

If a Machine only produces one product (column B) then I want it to state the product is C, If not then I want it to return the word Mixed (i.e. Machine C produces three products). The rows in B are not unique and looking for vba code to populate C (hoping please to keep the spreadsheet as is, i.e. not remove duplicates in B or sort etc).

Much appreciated

Wallace

**Tinbendr** · 06-26-2012, 08:49 AM

Column C

=IF(COUNTIF($A:$A,A1)> 1, A1 & " Mixed", A1)

**Wallace_Gren** · 06-26-2012, 06:58 PM

Hi David,

Thanks for your time. Unfortunately Column B is not considered in your formula above and if A is unique relative to B then it is fine (doesn't matter how many times ProductC appears under Machine A). i.e.

Example1
ProductC MachineA = ProductC
ProductC MachineA = ProductC
ProductC MachineA= ProductC

Example2
ProductC MachineA = Mixed
ProductB MachineA = Mixed
ProductC MachineA= Mixed

As MachineA now produces productC and productB so it is Mixed in example 1 it only produces productC. I have 8000 rows+ but don't want to use for example removeduplicates then do a count if unique.

Any other suggestions would be very welcome.

Wallace

**CodeNinja** · 06-27-2012, 06:24 AM

Wallace_Gren,
This one took me a while to figure out... you need to use an excel ARRAY formula in your cell. I am not the most fluent in array formulas, so it took me some time. Frankly, most users don't even know excel arrays formulas exist, but they are very powerful. If you want to read up on it, I suggest reading this article:

http://www.mrexcel.com/articles/CSE-...ulas-excel.php

So, looking at your first post, if you had Column A as product and Column B as Machine, and rows 1-5 filled, you would use the following formula:

=IF(SUM(IF(($A$1:$A$5=A1)*($B$1:$B$5=B1),1,0))>1,"Mixed",A1)

Now, that will not work if you just put it in, you need to then click on the formula bar and hold down control, shift and then press enter and let go of all 3. This will but little brackets {} around the formula and it will become an array formula.

Now that you have done that, drag the formula down to the other cells.

If you need more help, let me know, and thanks for the challenge.

**CodeNinja** · 06-27-2012, 06:46 AM

Wallace_Gren

GAH!!!! I got it backwards... lol...

In the formula it should be =1 not >1... so use:

=IF(SUM(IF(($A$1:$A$5=A1)*($B$1:$B$5=B1),1,0))=1,"Mixed",A1)

You will still have to ctrl shift enter and all that jazz....

Sorry about that.

**CodeNinja** · 06-27-2012, 07:04 AM

HRM... not quite right... still playing with this... will let you know soon...

**CodeNinja** · 06-27-2012, 07:12 AM

Ok... I am pretty sure this is the correct formula you want...

=IF(COUNT(IF(($B$1:$B$7=$B1)*($A$1:$A$7<>$A1),1))>0,"Mixed",$B1)

Same as before, still have to do the shift, control, enter thing for it to work properly.

Sorry about that.

**Tinbendr** · 06-27-2012, 08:10 AM

Arg! I could only get it to work with a helper column.

D1 =B1&A1
C1 =IF(COUNTIF($D:$D,B1&A1)>1,"Mixed", A1)

**Wallace_Gren** · 06-29-2012, 08:16 AM

Thanks Tinbendr and codeNinja for your time. It actually got sorted with vba..if we you would like me to post the code solution for your interest let me know

**Wallace_Gren** · 06-29-2012, 08:26 AM

Originally Posted by CodeNinja

Ok... I am pretty sure this is the correct formula you want...

=IF(COUNT(IF(($B$1:$B$7=$B1)*($A$1:$A$7<>$A1),1))>0,"Mixed",$B1)

Same as before, still have to do the shift, control, enter thing for it to work properly.

Sorry about that.

Just to let you know I tried your formula and it works!!! If I didn't have calculations switched off due to the size of the workbook would use this instead of code Thanks CodeNinja....Wallace

**Tinbendr** · 06-29-2012, 09:22 AM

Originally Posted by Wallace_Gren

if you would like me to post the code solution for your interest let me know

Always!

How many rows are there?

I actually had the VBA solution about half way finished, but got hungup on one section, then got busy doing other things.

**Wallace_Gren** · 06-29-2012, 06:46 PM

Originally Posted by Tinbendr

Always!

How many rows are there?

I actually had the VBA solution about half way finished, but got hungup on one section, then got busy doing other things.

***I cannot take credit for this in anyway, MickG from another forum provided this******

Assume answer in C starting row 2.
There are at present 300 rows of information changing daily i.e. new machines added and products produced but intention is this will be able to be used elsewhere on 8000+ rows

Sub MGMrexcel
Dim Rng         As Range
Dim Dn          As Range
Dim Temp        As String
Dim Q
Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
   .CompareMode = vbTextCompare
   For Each Dn In Rng
          If Not .Exists(Dn.Value) Then
      .Add Dn.Value, Array(Dn, Dn.Offset(, -1))
      Dn.Offset(, 1) = .Item(Dn.Value)(1)
          Else
      Q = .Item(Dn.Value)
                  Set Q(0) = Union(Q(0), Dn)
                      If Not Dn.Offset(, -1) = Q(1) Then
         Q(1) = "Mixed"
                      End If
      Q(0).Offset(, 1).Value = Q(1)
      .Item(Dn.Value) = Q
       End If
   Next Dn
End With
End Sub

**shrivallabha** · 07-01-2012, 03:06 AM

If you are going to run this code for large data set then you may try Array instead of range to process data. That way it will be shade faster for smaller data and considerably faster with large data.

Public Sub RunWallaceCode()
Dim varRng As Variant
Dim objDic As Object
Dim i As Long
varRng = Range("A2:C" & Range("B" & Rows.Count).End(xlUp).Row).Value
Set objDic = CreateObject("Scripting.Dictionary")
With objDic
   .comparemode = vbTextCompare
   For i = LBound(varRng) To UBound(varRng)
      If Not .exists(varRng(i, 2)) Then
         .Add varRng(i, 2), varRng(i, 1)
         Else
         If .Item(varRng(i, 2)) <> varRng(i, 1) Then _
            .Item(varRng(i, 2)) = .Item(varRng(i, 2)) & "|" & varRng(i, 1)
         End If
   Next I
   For i = LBound(varRng) To UBound(varRng)
      If InStr(.Item(varRng(i, 2)), "|") > 0 Then
         varRng(i, 3) = "mixed"
         Else
         varRng(i, 3) = varRng(i, 1)
      End If
    Next i
End With
Range("A2").Resize(UBound(varRng), 3) = varRng
End Sub

**Wallace_Gren** · 07-02-2012, 07:53 PM

Originally Posted by shrivallabha

If you are going to run this code for large data set then you may try Array instead of range to process data. That way it will be shade faster for smaller data and considerably faster with large data.

Public Sub RunWallaceCode()
Dim varRng As Variant
Dim objDic As Object
Dim i As Long
varRng = Range("A2:C" & Range("B" & Rows.Count).End(xlUp).Row).Value
Set objDic = CreateObject("Scripting.Dictionary")
With objDic
   .comparemode = vbTextCompare
   For i = LBound(varRng) To UBound(varRng)
      If Not .exists(varRng(i, 2)) Then
         .Add varRng(i, 2), varRng(i, 1)
         Else
         If .Item(varRng(i, 2)) <> varRng(i, 1) Then _
            .Item(varRng(i, 2)) = .Item(varRng(i, 2)) & "|" & varRng(i, 1)
         End If
      Next I
      For i = LBound(varRng) To UBound(varRng)
         If InStr(.Item(varRng(i, 2)), "|") > 0 Then
            varRng(i, 3) = "mixed"
            Else
            varRng(i, 3) = varRng(i, 1)
         End If
      Next i
End With
Range("A2").Resize(UBound(varRng), 3) = varRng
End Sub

Thanks shrivallabha for your input, I have amended my code as suggested in an array

Thread: Compare Two Columns and return text

Thread Tools

Display

Compare Two Columns and return text

Posting Permissions