Excel 2010: Lookup & Remove Duplicate Values

[kmaxx98]

New user, so excuse me if I step all over posting etiquette on this go round

(I am trying to figure out how to attach the file, but haven't figured out how yet, sorry)

I have a long list of dates (a few years worth) and Work Orders, and need to lookup the list of Work Orders associated with a given date. The issue I run into is that the Work Orders are listed by shift, and therefore can "duplicate" within a day. I need to get the list without the duplicate data, in the order they ran.

Date</SPAN>	Shift</SPAN>	Work Order</SPAN>
6/1/2012</SPAN>	1</SPAN>	1001</SPAN>
6/1/2012</SPAN>	1</SPAN>	1002</SPAN>
6/1/2012</SPAN>	2</SPAN>	1002</SPAN>
6/1/2012</SPAN>	2</SPAN>	1003</SPAN>
6/1/2012</SPAN>	3</SPAN>	1003</SPAN>
6/1/2012</SPAN>	3</SPAN>	1004</SPAN>
6/1/2012</SPAN>	4</SPAN>	1005</SPAN>
6/1/2012</SPAN>	4</SPAN>	1006</SPAN>
6/2/2012</SPAN>	1</SPAN>	1006</SPAN>
6/2/2012</SPAN>	1</SPAN>	1006</SPAN>
6/2/2012</SPAN>	2</SPAN>	1006</SPAN>
6/2/2012</SPAN>	2</SPAN>	1007</SPAN>
6/2/2012</SPAN>	3</SPAN>	1007</SPAN>
6/2/2012</SPAN>	3</SPAN>	1008</SPAN>
6/2/2012</SPAN>	4</SPAN>	1008</SPAN>
6/2/2012</SPAN>	4</SPAN>	1009</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

I have created an array formula which I am hoping some Jedi Excel Master out there can modify to help me remove the duplicate values:

{=IF(ISERROR(INDEX($A$1:$C$17,SMALL(IF($A$1:$A$17=$A$20,ROW($A$1:$A$17)),ROW(1:1)),3)),"",INDEX($A$1:$C$17,SMALL(IF($A$1:$A$17=$A$20,ROW($A$1:$A$17)),ROW(1:1)),3))}

Below are my current result and desired result:

Current Formula</SPAN>
6/2/2012</SPAN>	1006</SPAN>
	1006</SPAN>
	1006</SPAN>
	1007</SPAN>
	1007</SPAN>
	1008</SPAN>
	1008</SPAN>
	1009</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

What I want it to show for 6/2…</SPAN>
6/2/2012</SPAN>	1006</SPAN>
	1007</SPAN>
	1008</SPAN>
	1009</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=2></COLGROUP>

Thanks, and if someone can advise me how to upload the file, it might make it easier to understand!

 

[adam087]

Have you tried using a Pivot Table?

Select your data, then go to Insert -> Pivot Table; click OK; drag 'Date' and 'Work Order' to the 'Row Labels' box on the right hand side.

That should give you what you need

Regards
Adam

 

[Marcelo Branco]

Maybe something like this


A B C D E

Date	Shift	Work Order	Reference Date	Orders
06/01/2012	1	1001	06/02/2012	1006
06/01/2012	1	1002		1007
06/01/2012	2	1002		1008
06/01/2012	2	1003		1009
06/01/2012	3	1003
06/01/2012	3	1004
06/01/2012	4	1005
06/01/2012	4	1006
06/02/2012	1	1006
06/02/2012	1	1006
06/02/2012	2	1006
06/02/2012	2	1007
06/02/2012	3	1007
06/02/2012	3	1008
06/02/2012	4	1008
06/02/2012	4	1009

<tbody>
</tbody>

Array formula in E2 (Excel 2007 or higher)
=IFERROR(INDEX($C$2:$C$1000,SMALL(IF($A$2:$A$1000=$D$2,IF(COUNTIFS(OFFSET($A$2,,,ROW($A$2:$A$1000)-ROW($A$2)+1),$D$2,OFFSET($C$2,,,ROW($C$2:$C$1000)-ROW($C$2)+1),$C$2:$C$1000)=1,ROW($C$2:$C$1000)-ROW($C$2)+1)),ROWS($1:1))),"")

confirmed with Ctrl+Shift+Enter

copy down

M.

 

[kmaxx98]

Hey Adam,

Yeah, thanks, I could do that, but I am trying to build a tool for our group and the data will be input daily, so I am trying to find a formula which will populate without having to refresh a pivot.

Any ideas?

Have you tried using a Pivot Table?

Select your data, then go to Insert -> Pivot Table; click OK; drag 'Date' and 'Work Order' to the 'Row Labels' box on the right hand side.

That should give you what you need

Regards
Adam[/QUO

 

[kmaxx98]

Marco,

That is genius. It works, I understand it enough to fit it in to my file.

Would you be kind enough to explain what you modified by chance (ie how the count ifs/offset combination works)?

Maybe something like this


A B C D E

Date	Shift	Work Order	Reference Date	Orders
06/01/2012	1	1001	06/02/2012	1006
06/01/2012	1	1002		1007
06/01/2012	2	1002		1008
06/01/2012	2	1003		1009
06/01/2012	3	1003
06/01/2012	3	1004
06/01/2012	4	1005
06/01/2012	4	1006
06/02/2012	1	1006
06/02/2012	1	1006
06/02/2012	2	1006
06/02/2012	2	1007
06/02/2012	3	1007
06/02/2012	3	1008
06/02/2012	4	1008
06/02/2012	4	1009

<TBODY>
</TBODY>

Array formula in E2 (Excel 2007 or higher)
=IFERROR(INDEX($C$2:$C$1000,SMALL(IF($A$2:$A$1000=$D$2,IF(COUNTIFS(OFFSET($A$2,,,ROW($A$2:$A$1000)-ROW($A$2)+1),$D$2,OFFSET($C$2,,,ROW($C$2:$C$1000)-ROW($C$2)+1),$C$2:$C$1000)=1,ROW($C$2:$C$1000)-ROW($C$2)+1)),ROWS($1:1))),"")

confirmed with Ctrl+Shift+Enter

copy down

M.

 

[Marcelo Branco]

Hi,

The COUNTIFS uses the 4th parameter of OFFSET (height) to increase the range by one row successively, since this part
ROW(A2:A1000)-ROW(A2)+1
returns an array like
{1;2;3......................1000}

(the same idea for column C)

Then the COUNTIFS applies to
A2:C2 (height=1)
A2:C3 (height=2)
A2:C4 (height=3)
and so on

returnuing 1 only to first pair of values, the reference date in column A and the Work Order in column C.

To see how this works select, say, G2:G17 and enter this formula in the formula-bar
=COUNTIFS(OFFSET($A$2,,,ROW($A$2:$A$17)-ROW($A$2)+1),$D$2,OFFSET($C$2,,,ROW($C$2:$C$17)-ROW($C$2)+1),$C$2:$C$17)
Ctrl+Shift+Enter

You see this

G (beginning in row 2)

0
0
0
0
0
0
0
0
1
2
3
1
2
1
2
1

<tbody>
</tbody>

Hope i made my self clear

M.

 

[kmaxx98]

Hi Marcelo,

That was a great explanation, thanks.

I was curious, if I wanted to inject the condition by shift as well, where would I inject that into the formula?

So 6/2 provides 4 results, but 6/2 1st shift would only provide WO #1006. Does that make sense?

Hi,

The COUNTIFS uses the 4th parameter of OFFSET (height) to increase the range by one row successively, since this part
ROW(A2:A1000)-ROW(A2)+1
returns an array like
{1;2;3......................1000}

(the same idea for column C)

Then the COUNTIFS applies to
A2:C2 (height=1)
A2:C3 (height=2)
A2:C4 (height=3)
and so on

returnuing 1 only to first pair of values, the reference date in column A and the Work Order in column C.

To see how this works select, say, G2:G17 and enter this formula in the formula-bar
=COUNTIFS(OFFSET($A$2,,,ROW($A$2:$A$17)-ROW($A$2)+1),$D$2,OFFSET($C$2,,,ROW($C$2:$C$17)-ROW($C$2)+1),$C$2:$C$17)
Ctrl+Shift+Enter

You see this

G (beginning in row 2)

0
0
0
0
0
0
0
0
1
2
3
1
2
1
2
1

<TBODY>
</TBODY>

Hope i made my self clear

M.

 

[Marcelo Branco]

Try the array formula below in, say, G2

Assumes
Reference Date in D2 = 06/02/2012
Shift in F2 = 1 (for example)

=IFERROR(INDEX($C$2:$C$1000,SMALL(IF($A$2:$A$1000=$D$2,IF($B$2:$B$1000=$F$2,IF(COUNTIFS(OFFSET($A$2,,,ROW($A$2:$A$1000)-ROW($A$2)+1),$D$2,OFFSET($B$2:$B$1000,,,ROW($B$2:$B$1000)-ROW($B$2)+1),$B$2:$B$1000,OFFSET($C$2,,,ROW($C$2:$C$1000)-ROW($C$2)+1),$C$2:$C$1000)=1,ROW($C$2:$C$1000)-ROW($C$2)+1))),ROWS($1:1))),"")

Ctrl+Shift+Enter

copy down

M.