View Full Version : 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-array-formulas-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

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.

:friends: 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

if you would like me to post the code solution for your interest let me knowAlways! :)

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

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

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 :friends:

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.