Extract unique data from a table - pivot table or something else ?

[skyrat]

I am trying to extract unique data from a table which at first I tried to do using a pivot table, but it needs some further processing or a different technique.

Below is a simplified example to explain what I am trying to do.

I have a set of data with headers 'From' and 'To'.

The first row shows that A goes to 1, then half way down the table this data is reversed, so 1 goes to A etc

I am trying to extract the unique data from this table, because the second half of the table is duplicated, its just the "From" and "To" that is in vice versa

i.e. "From" A "To" 1 is the same as "From 1 "To" A

The result I am looking for would give this...

i.e. it would recognise that "A to 1" is the same as "1 to A" and only give one of those results (either "A to 1" or "1 to A")

The data will actually have multiple From's and To's as shown below but they are always the same.

In the example below there are multiple "C to 3" (and vice versa "3 to C") as well as multiple "E to 5" (and vice versa "5 to E")

However, the result must still give this...

I tried a pivot table which correctly deals with the multiple From's and To's, but it also shows all the duplicate vice versa's

i.e. "1 to A" as well as "A to 1" - these are the same just in the reverse order. I want it to only show one of these (it does not matter which)

Can a pivot table be used to do this ?

Or is there an existing function ?

Or some other technique ?

 

[RasGhul]

Can you let us know what Version of Excel?

 

[Peter_SSs]

Is your real data actually like that (single letter and single digit)?
Does it matter what order the results come in? For example, with the sample data in columns A:B below, is the result in cols D:E acceptable or must it be like cols G:H?

skyrat.xlsm
	A	B	C	D	E	F	G	H
1
2	A	1		A	1		A	1
3	B	2		B	2		B	2
4	1	A		3	C		C	3
5	3	C
6	C	3
7	2	B
Sheet2

As well as telling us your Excel version - when you do please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’) - you might also investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[mohadin]

What about a code such as:

Sub test()
Dim LR As Long
Dim I As Long
Dim A1, A2 As Variant
LR = Cells(Rows.Count, 1).End(xlUp).Row - 1
A1 = Cells(1, 1).Resize(LR / 2, 2)
A2 = Application.Index(Cells(2, 1).Offset(LR / 2).Resize(LR / 2, 2), Evaluate("ROW(1:" & LR / 2 & ")"), Array(2, 1))
 With CreateObject("scripting.dictionary")
  For I = 1 To UBound(A1)
    If Not .Exists(A1(I, 1)) Then
    .Add A1(I, 1), A1(I, 2)
    Else
    If Not .Exists(A2(I + LR / 2, 1)) Then .Add A2(I, 1), A2(I, 2)
    End If
   Next
    Cells(2, 4).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
  End With
End Sub

 

[skyrat]

Thanks all

I managed to get it working - details of the solution is shown at the bottom of this post.

For a better understanding, I'll explain the application...

The spreadsheet is for a wiring harness, where the "From" and "To" are connector IDs

The connector IDs use a 4 digit number, so instead of From "A" and To "1" it could be From "1001" and To "6004"

Once the unique ID's have been extracted, I will be able to enter the wire length between the two connectors.

Below is a diagram to show the connector ID's and the wire length between the two connectors.

e.g. From connector 1001 to connector 6004 the length is 10cm

I have used the same ID's in my solution shown below.

I cant attach files and I don't have admin rights to install XL2BB, so I have shown the formulas underneath to explain how it works (and from what I can tell, it seems to work correctly).

PS. My Excel version is Office Professional Plus 2019

 

[Peter_SSs]

My Excel version is Office Professional Plus 2019

Please add that to your Account details (click your user name at the top right of the forum) so helpers always know. (Don’t forget to scroll down & ‘Save’)

If you are happy with the layout shown in your solution, then you could do it more directly like this.

21 11 13.xlsm
	A	B	C	D	E	F	G
1
2
3	1001	6004				1001:6004	6004:1001
4	8015	7006				8015:7006	7006:8015
5	1027	3002				1027:3002	3002:1027
6	2006	9005				2006:9005	9005:2006
7	8015	7006
8	4003	1015				4003:1015	1015:4003
9	1027	3002
10	3008	5012				3008:5012	5012:3008
11	1027	3002
12	8015	7006
13	5007	8011				5007:8011	8011:5007
14	2002	3021				2002:3021	3021:2002
15	6004	1001
16	7006	8015
17	3002	1027
18	9005	2006
19	7006	8015
20	1015	4003
21	3002	1027
22	5012	3008
23	3002	1027
24	7006	8015
25	8011	5007
26	3021	2002
Combos

Cell Formulas
Range	Formula
F3:F26	F3	=IF(COUNTIF(F$2:F2,A3&":"&B3)+COUNTIF(F$2:F2,B3&":"&A3),"",A3&":"&B3)
G3:G26	G3	=IF(F3="","",RIGHT(F3,4)&":"&LEFT(F3,4))