Help with dynamic unique list

Status
Not open for further replies.

qwertytrewq

New Member
Joined
Apr 19, 2018
Messages
9
i have data from which i need to extract unique list. i have everything up and running but my data has lot more rows n columns and excel takes forever to calculate it. Please suggest way to simplify these formula to cut back on processing time or any other way to get the desired dynamic list.

Column AColumn BColumn CColumn DColumn EColumn FColumn GColumn HColumn IColumn JColumn KColumn LColumn MColumn NColumn O
Below is Sample DataBelow is Calculation step to get Unique Value in Sequence as in Sample DataBelow is Desired Dynamic List obtained from Sample Data
SampleData related to the sampleSampleData related to the names
First NameLast Namedata1data2data3First NameLast Namedata1data2data3
JohnDoexyzJohn ; DoeJane ; DouJohn ; DowJohnDowzxy
JohnDowzxyJohn ; DowJane ; DowJohn ; DouJohnDouxzy
JohnDouxzyJohn ; DouJane ; DoeJohn ; DoeJohnDoexyz
JohnDoeyzxJohn ; DoeJohn ; DoeJane ; DoeJaneDoeyxz
JaneDoeyxzJane ; DoeJohn ; DouJane ; DowJaneDowzyx
JaneDowzyxJane ; DowJohn ; DowJane ; DouJaneDouxxx
JaneDouxxxJane ; Dou
JaneDouyyyJane ; Dou

<colgroup><col width="94" span="15" style="width:71pt"> </colgroup><tbody>
</tbody>


Formula in G5 : =A5&" ; "&B5
Formula in H5 : =IFERROR(LOOKUP(2,1/(COUNTIF($H$4:H4,$G$5:$G$12)=0),$G$5:$G$12),"")
Formula in I5 : =IFERROR(LOOKUP(2,1/(COUNTIF($I$4:I4,$H$5:$H$12)=0),$H$5:$H$12),"")
Formula in K5 : =LEFT(I5,SEARCH(" ; ",I5)-1)
Formula in L5 : =RIGHT(I5,LEN(I5)-SEARCH(" ; ",I5)-2)
Formula in M5 : =INDEX($C$5:$C$12,MATCH(1,INDEX((K5=$A$5:$A$12)*(L5=$B$5:$B$12),0,1),0))
Formula in N5 : =INDEX($D$5:$D$12,MATCH(1,INDEX((K5=$A$5:$A$12)*(L5=$B$5:$B$12),0,1),0))
Formula in O5 : =INDEX($E$5:$E$12,MATCH(1,INDEX((K5=$A$5:$A$12)*(L5=$B$5:$B$12),0,1),0))


Thanks for looking into this.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

qwertytrewq

New Member
Joined
Apr 19, 2018
Messages
9
Correction in table - Shift H4 three cells to right

Column AColumn BColumn CColumn DColumn EColumn FColumn GColumn HColumn IColumn JColumn KColumn LColumn MColumn NColumn O
Below is Sample DataBelow is Calculation step to get Unique Value in Sequence as in Sample DataBelow is Desired Dynamic List obtained from Sample Data
SampleData related to the sampleSampleData related to the names
First NameLast Namedata1data2data3First NameLast Namedata1data2data3
JohnDoexyzJohn ; DoeJane ; DouJohn ; DowJohnDowzxy
JohnDowzxyJohn ; DowJane ; DowJohn ; DouJohnDouxzy
JohnDouxzyJohn ; DouJane ; DoeJohn ; DoeJohnDoexyz
JohnDoeyzxJohn ; DoeJohn ; DoeJane ; DoeJaneDoeyxz
JaneDoeyxzJane ; DoeJohn ; DouJane ; DowJaneDowzyx
JaneDowzyxJane ; DowJohn ; DowJane ; DouJaneDouxxx
JaneDouxxxJane ; Dou
JaneDouyyyJane ; Dou

<colgroup><col width="94" span="15" style="width:71pt"> </colgroup><tbody>
</tbody>
 

qwertytrewq

New Member
Joined
Apr 19, 2018
Messages
9
Correction - Shift H3 three cells to the right i.e. "Sample" to be in K3:L3 and "Data related to the names" in M3:MO3
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,108,684
Messages
5,524,272
Members
409,567
Latest member
Shadh

This Week's Hot Topics

Top