Keep 1st instance

[junkforhr]

I have a dataset which will have columns of Item and Status ( there are more columns but these are the two I need to use for my result)

Basically create a filter or use VBA to create a new dataset where I find duplicates of the combination of Item and Status, but keep the 1st instance of that combination but also keep every other row of data.

In the example below Item "5" and Status of "Ready" is in the data twice and also Item "7" and Status of "Assign" is there twice. I want to keep the 1st instance of both Item 5 and 7 and also Item 6.

The actual data set is 500K rows, so would like a filter so I can create a pivot table and use that to create the new dataset or VBA.

Item	Status
5​	Ready
5​	Ready
6​	ABC
7​	Assign
7​	Assign

Desired Outcome

Item	Status
5​	Ready
6​	ABC
7​	Assign

 

[awoohaw]

I don't know what you mean by "keeping every other row of data". But, this gets you the desired results you list:

Mr Excel Questions 72.xlsm
	A	B	C	D	E	F
1
2	Item	Status		Item	Status
3	5	Ready		5	Ready
4	5	Ready		6	ABC
5	6	ABC		7	Assign
6	7	Assign
7	7	Assign
8
junkforhr

Cell Formulas
Range	Formula
D2:E5	D2	=UNIQUE(A2:B7,FALSE)
Dynamic array formulas.

 

[junkforhr]

Apologies I should have have mentioned that I want to keep the 1sy instance of the combination of Item and where Status equals "Ready" or "Assign", so 5Ready where only have 1 row and and 7Assign would have 1 row, but for any other combination I would need duplicates. So if there were 2 rows of 6ABC, then that would have 2 rows.

 

[awoohaw]

Maybe this then?

Mr Excel Questions 72.xlsm
	A	B	C	D	E
1
2	Item	Status
3	5	Ready		Item	Status
4	5	Ready		5	Ready
5	6	ABC		7	Assign
6	7	Assign		6	ABC
7	9	ABC		9	ABC
8	7	Assign
junkforhr

Cell Formulas
Range	Formula
D3:E7	D3	=VSTACK($A$2:$B$2,UNIQUE(FILTER($A$3:$B$8,(B3:B8="Ready")+(B3:B8="Assign"))),FILTER($A$3:$B$8,((B3:B8<>"Ready")+(B3:B8<>"Assign"))=2))
Dynamic array formulas.

 

[Peter_SSs]

Another option which keeps the relative order of the original data like the sample in post 1

23 11 08.xlsm
	A	B	C	D	E
1
2	Item	Status		Item	Status
3	5	Ready		5	Ready
4	5	Ready		6	ABC
5	6	ABC		7	Assign
6	7	Assign		9	ABC
7	9	ABC		6	Ready
8	7	Assign		6	ABC
9	6	Ready
10	7	Assign
11	6	ABC
12	6	Ready
junkforhr

Cell Formulas
Range	Formula
D2:E8	D2	=LET(ab,A2:A12&"|"&B2:B12,FILTER(A2:B12,(B2:B12<>"Ready")*(B2:B12<>"Assign")+(MATCH(ab,ab,0)=SEQUENCE(ROWS(ab)))))
Dynamic array formulas.

 

[junkforhr]

Thank you for providing these answers. Both work, so I updated as requested by my manager and now they've thrown ID into the mix, so the same logic but with ID included to create the unique values.

My apologies but I was not aware of this added requirement when I posted my original question.

Item	Status	ID
123​	Ready	ABC
123​	Ready	ABC
123​	Ready	ABC
123​	Unk	ABC
543​	Ready	BAC
543​	Ready	BAC
543​	UNK	BAC
543​	UNK	BAC
1​	Status1	ABC
2​	Assign	HJL
2​	Assign	HJL
4​	Status4	HJL
4​	Status5	HJL
1​	Status4	LPI
2​	Status1	LPI
3​	Status2	LPI
4​	Status3	LPI
5​	Status4	LPI

Desired Result

123​	Ready	ABC
123​	Unk	ABC
543​	Ready	BAC
543​	UNK	BAC
543​	UNK	BAC
1​	Status1	ABC
2​	Assign	HJL
4​	Status4	HJL
4​	Status5	HJL
1​	Status4	LPI
2​	Status1	LPI
3​	Status2	LPI
4​	Status3	LPI
5​	Status4	LPI

 

[bebo021999]

A VBA solution. see file attached.

Loading Google Sheets

Option Explicit
Sub combination()
Dim i&, j&, k&, rng, res(1 To 10000, 1 To 3), id As String
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
rng = Range("A2").CurrentRegion.Value ' A2: cell within 1st range
For i = 2 To UBound(rng)
    If i = UBound(rng) Then
        k = k + 1: res(k, 1) = rng(i, 1): res(k, 2) = rng(i, 2): res(k, 3) = rng(i, 3)
        Exit For
    End If
    id = rng(i, 1) & "|" & rng(i, 2) & "|" & rng(i, 3)
    If Not dic.exists(rng(i, 1)) Then
        dic.Add rng(i, 1), ""
        For j = i + 1 To UBound(rng)
            If rng(j, 1) & "|" & rng(j, 2) & "|" & rng(j, 3) <> id Then
                k = k + 1: res(k, 1) = rng(i, 1): res(k, 2) = rng(i, 2): res(k, 3) = rng(i, 3)
                i = j - 1
                Exit For
            End If
        Next
    Else
        k = k + 1: res(k, 1) = rng(i, 1): res(k, 2) = rng(i, 2): res(k, 3) = rng(i, 3)
    End If
Next
'PASTE RESULTS INTO CELL E2. ADJUST TO ANY OTHER CELL
Range("E2:G10000").ClearContents
Range("E2").Resize(k, 3).Value = res
End Sub

 

[Peter_SSs]

now they've thrown ID into the mix, so the same logic but with ID included to create the unique values.

Like this?

23 11 08.xlsm
	A	B	C	D	E	F	G
1	Item	Status	ID		Item	Status	ID
2	123	Ready	ABC		123	Ready	ABC
3	123	Ready	ABC		123	Unk	ABC
4	123	Ready	ABC		543	Ready	BAC
5	123	Unk	ABC		543	UNK	BAC
6	543	Ready	BAC		543	UNK	BAC
7	543	Ready	BAC		1	Status1	ABC
8	543	UNK	BAC		2	Assign	HJL
9	543	UNK	BAC		4	Status4	HJL
10	1	Status1	ABC		4	Status5	HJL
11	2	Assign	HJL		1	Status4	LPI
12	2	Assign	HJL		2	Status1	LPI
13	4	Status4	HJL		3	Status2	LPI
14	4	Status5	HJL		4	Status3	LPI
15	1	Status4	LPI		5	Status4	LPI
16	2	Status1	LPI
17	3	Status2	LPI
18	4	Status3	LPI
19	5	Status4	LPI
junkforhr (2)

Cell Formulas
Range	Formula
E1:G15	E1	=LET(ab,A1:A19&"|"&B1:B19&"|"&C1:C19,FILTER(A1:C19,(B1:B19<>"Ready")*(B1:B19<>"Assign")+(MATCH(ab,ab,0)=SEQUENCE(ROWS(ab)))))
Dynamic array formulas.