combining 3 columns w/o duplicates

[opal_1970]

Hi All,

I have three columns with data (A,B, and C), currently I have two columns, each with an Array. The first array (E) pulls instances out of A and B without any duplication, while the second array (G) does the same for C and E. This works fine to get all instances out of A,B, and C but I would like to do this in one step.

I am certain that this is a stupid logic error on my side, but when I try to combine them and have an array that pulls out instances from A, B, and C with out duplication I am not getting any output. Any advice is much appreciated.

Array in E (closed with Shift+Ctrl+Enter):

=IFERROR(IFERROR(INDEX(A:A; MATCH(0; COUNTIF(E$1:$E2; A:A);0)); INDEX(B:B; MATCH(0; COUNTIF(E$1:$E2;B:B); 0))); "")

Array in G (closed with Shift+Ctrl+Enter):

=IFERROR(IFERROR(INDEX(C:C; MATCH(0; COUNTIF(G$1:$G2; C:C);0)); INDEX(E:E; MATCH(0; COUNTIF(G$1:$G2;E:E); 0))); "")

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Tom
[/TD]
[TD]Hardy
[/TD]
[TD]Harriette
[/TD]
[TD][/TD]
[TD]Tom
[/TD]
[TD][/TD]
[TD]Tom
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Dan
[/TD]
[TD]Tom
[/TD]
[TD]Hardy
[/TD]
[TD][/TD]
[TD]Dan
[/TD]
[TD][/TD]
[TD]Dan
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Harry
[/TD]
[TD]Julia
[/TD]
[TD]Becky
[/TD]
[TD][/TD]
[TD]Harry
[/TD]
[TD][/TD]
[TD]Harry
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Julia
[/TD]
[TD][/TD]
[TD]Julia
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Hardy
[/TD]
[TD][/TD]
[TD]Hardy
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Becky
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Harriette[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

@Marcelo Branco

What if the OP wants to begin the list in row 1?

Regards

A possible solution

in E1
=A1

Insert the formula in E2

M.

 

[azumi]

Hi All,

I have three columns with data (A,B, and C), currently I have two columns, each with an Array. The first array (E) pulls instances out of A and B without any duplication, while the second array (G) does the same for C and E. This works fine to get all instances out of A,B, and C but I would like to do this in one step.

I am certain that this is a stupid logic error on my side, but when I try to combine them and have an array that pulls out instances from A, B, and C with out duplication I am not getting any output. Any advice is much appreciated.

Array in E (closed with Shift+Ctrl+Enter):

=IFERROR(IFERROR(INDEX(A:A; MATCH(0; COUNTIF(E$1:$E2; A:A);0)); INDEX(B:B; MATCH(0; COUNTIF(E$1:$E2;B:B); 0))); "")

Array in G (closed with Shift+Ctrl+Enter):

=IFERROR(IFERROR(INDEX(C:C; MATCH(0; COUNTIF(G$1:$G2; C:C);0)); INDEX(E:E; MATCH(0; COUNTIF(G$1:$G2;E:E); 0))); "")

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Tom[/TD]
[TD]Hardy[/TD]
[TD]Harriette[/TD]
[TD][/TD]
[TD]Tom[/TD]
[TD][/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Dan[/TD]
[TD]Tom[/TD]
[TD]Hardy[/TD]
[TD][/TD]
[TD]Dan[/TD]
[TD][/TD]
[TD]Dan[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Harry[/TD]
[TD]Julia[/TD]
[TD]Becky[/TD]
[TD][/TD]
[TD]Harry[/TD]
[TD][/TD]
[TD]Harry[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Julia[/TD]
[TD][/TD]
[TD]Julia[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Hardy[/TD]
[TD][/TD]
[TD]Hardy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Becky[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Harriette[/TD]
[/TR]
</tbody>[/TABLE]

I see whats wrong in your formula, the formula position needs in second row, and change this:

COUNTIF(E$1:$E2; A:A)

and

COUNTIF(G$1:$G2; C:C)

to

COUNTIF(E$1:$E1; A:A)

and

COUNTIF(G$1:$G1; C:C)

and for fast process you need to adjust your ranges in formula with your table, like INDEX(A:A --> this will slow your computer down, better if like this INDEX(A1:A3

My Version Formula, put this in exactly in cell A6:
=IFERROR(INDEX($A$1:$A$3,MATCH(0,COUNTIF($A$5:A5,$A$1:$A$3),0)),IFERROR(INDEX($B$1:$B$3,MATCH(0,COUNTIF($A$5:A5,$B$1:$B$3),0)),IFERROR(INDEX($C$1:$C$3,MATCH(0,COUNTIF($A$5:A5,$C$1:$C$3),0)),"")))

Array Formula, when ENTERING the formula needs to press CTRL+SHIFT+ENTER button together, and then copied down.

 

[XOR LX]

My formula doesn't work. We would need to create another formula. Simple as that

Ideas?

M.

The solution I gave earlier!

Regards

 

[Marcelo Branco]

Yes, there are many possible solutions.

Regards,

M.

 

[opal_1970]

First of all thanks everyone for your input, I am still trying the various suggestions, but I think I am getting there.

...and for fast process you need to adjust your ranges in formula with your table, like INDEX(A:A --> this will slow your computer down, better if like this INDEX(A1:A3

Yes, performance is a big issue at the moment. This is only a part of one formula, and not even one of the more complex ones and if I calculate the entire workbook, it generally takes anywhere from 5 to 10 minutes to complete. so I will certainly try someting in this area.

the problem with this specific formula is that the columns are from three different pivot tables that change dynamically... getting their information from an SQL query (table) containing anywhere between 5000 to 40 000 lines. I would assume that any of the pivot tables would never have any more than say, 20 to 40 lines. so I could theoretically limit it to that, but missing a line would be very business critical and I am not sure what would be a "comfortable" number of rows.

 

[opal_1970]

Unique list of names with "har"

Array formula in G2 copied down
=IFERROR(INDIRECT(TEXT(MIN(IF($A$1:$C$6<>"",IF(ISNUMBER(SEARCH("har",$A$1:$C$6)),IF(COUNTIF(G$1:G1,$A$1:$C$6)=0,ROW($A$1:$C$6)*10^4+COLUMN($A$1:$C$6))))),"R0000C0000"),FALSE),"")

confirmed with Ctrl+Shift+Enter

Hi Marcelo,
can you tell me what the format text value in: TEXT("R0000C0000") is?
but this looks very promising.

 

[Marcelo Branco]

Hi Marcelo,
can you tell me what the format text value in: TEXT("R0000C0000") is?
but this looks very promising.

1. Remembering INDIRECT syntax

INDIRECT(ref_text,a1)
where
ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string.

and

a1 is a logical value that specifies what type of reference is contained in the cell ref_text.
If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

2. My Formula

=IFERROR(INDIRECT(TEXT(MIN(IF($A$1:$C$6<>"",IF(COUNTIF(E$1:E1,$A$1:$C$6)=0,ROW($A$1:$C$6)*10^4+COLUMN($A$1:$C$6)))),"R0000C0000"),FALSE),"")

In my formula a1, the second argument of the INDIRECT, is set to FALSE (in red above) . So the ref-text is interpreted as an R1C1 style reference.

At last...

TEXT("R0000C0000") - where R is a short for Row and C a short for Column - creates a ref-text in R1C1 style that can be properly interpreted by the INDIRECT function.

For example TEXT(20001,"R0000C0000") results in R0002C0001 and INDIRECT returns the value of cell in row 2, column 1 (cell A2 in A1-style)

M.

 

[XOR LX]

Actually, "R0C0000" is sufficient.

And technically we should multiply the ROW construction by 10^5, since, with 16,384 columns in a worksheet, 10^4 is not guaranteed to be sufficient (although it almost certainly will be).

Of course, the format_text parameter for the TEXT function is then necessarily "R0C00000".

Regards

 

[Marcelo Branco]

Thanks for the pertinent comments

Regards

M.