Unique list.

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,353
Office Version
  1. 2010
Hello,

I have got in column "A" SN and in Column "B" Year (Note Column "B" is formatted As Text)

From these data I want to have unique list....
In the column "E" unique years
In the column "F" Starting Year SN Number
In the column "G" End Year SN Number
In the column "H" Starting Year Line Number
In the column "I" End Year Line Number

Example image is attached for more details

*ABCDEFGHIJK
1
2
3
4
5SNYearYearStarting Year SN NumberEnd Year SN NumberStarting Year Line NumberEnd Year Line Number
6170/7170/711368
7270/7186/8758912
8370/7187/889121316
9586/8790/9122281723
10686/8792/9311212434
11786/8794/952133546
12886/8709/1054614754
13987/8810/11165560
141087/8813/1426346169
151187/88
161287/88
172290/91
182390/91
192490/91
202590/91
212690/91
222790/91
232890/91
241192/93
251292/93
261392/93
271492/93
281592/93
291692/93
301792/93
311892/93
321992/93
332092/93
342192/93
35294/95
36394/95
37494/95
38594/95
39694/95
40794/95
41894/95
42994/95
431094/95
441194/95
451294/95
461394/95
475409/10
485509/10
495609/10
505709/10
515809/10
525909/10
536009/10
546109/10
55110/11
56210/11
57310/11
58410/11
59510/11
60610/11
612613/14
622713/14
632813/14
642913/14
653013/14
663113/14
673213/14
683313/14
693413/14
70
71
72
73
74
75
76
77
78
79
80

Thank you all.

I am using Excel 2000

Regards,
Moti
 

Attachments

  • Make A Unique List.png
    Make A Unique List.png
    78.2 KB · Views: 3

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Assuming that the real sheet is consistent with the example.
Book1
EFGHI
5YearStarting Year SN NumberEnd Year SN NumberStarting Year Line NumberEnd Year Line Number
670/711368
786/8758912
887/889121316
990/9122281723
1092/9311212434
1194/952133546
1210/2154614754
1311/21165560
1413/1426346169
15     
Sheet1
Cell Formulas
RangeFormula
E6:E15E6=IF(ISNA(INDEX($B$6:$B$69,MATCH(TRUE,ISNA(MATCH($B$6:$B$69,$E$5:$E5,0)),0))),"",INDEX($B$6:$B$69,MATCH(TRUE,ISNA(MATCH($B$6:$B$69,$E$5:$E5,0)),0)))
F6:G15F6=IF($E6="","",INDEX($A:$A,H6))
H6:H15H6=IF(E6="","",MATCH(E6,$B:$B,0))
I6:I15I6=IF(E6="","",IF(E7="",MATCH("zzz",B:B,1),MATCH(E7,B:B,0)-1))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Assuming that the real sheet is consistent with the example.
Book1
EFGHI
5YearStarting Year SN NumberEnd Year SN NumberStarting Year Line NumberEnd Year Line Number
670/711368
786/8758912
887/889121316
990/9122281723
1092/9311212434
1194/952133546
1210/2154614754
1311/21165560
1413/1426346169
15     
Sheet1
Cell Formulas
RangeFormula
E6:E15E6=IF(ISNA(INDEX($B$6:$B$69,MATCH(TRUE,ISNA(MATCH($B$6:$B$69,$E$5:$E5,0)),0))),"",INDEX($B$6:$B$69,MATCH(TRUE,ISNA(MATCH($B$6:$B$69,$E$5:$E5,0)),0)))
F6:G15F6=IF($E6="","",INDEX($A:$A,H6))
H6:H15H6=IF(E6="","",MATCH(E6,$B:$B,0))
I6:I15I6=IF(E6="","",IF(E7="",MATCH("zzz",B:B,1),MATCH(E7,B:B,0)-1))
Press CTRL+SHIFT+ENTER to enter array formulas.
jasonb75, I am very sorry for replying you late after applying your formula as per post#1 given layout all worked as request. Extending the range in formula E6 from "$B$6:$B$69" to $B$6:$B$100 and coping down formula to E100 it produced the error #N/A in the cells G14 and I14.

Just a note: To remove the error #N/A I did change the formulas as show below
VBA Code:
Formula in F6=IF(ISERROR(IF($E6="","",INDEX($A:$A,H6))),"",IF($E6="","",INDEX($A:$A,H6)))

Formula in G6=IF(ISERROR(IF($E6="","",INDEX($A:$A,I6))),"",IF($E6="","",INDEX($A:$A,I6)))

Formula in H6 =IF(ISERROR(IF(E6="","",MATCH(E6,$B:$B,0))),"",IF(E6="","",MATCH(E6,$B:$B,0)))

Formula in I6 =IF(ISERROR(IF(E6="","",IF(E7=0,MATCH("zzz",B:B,1),MATCH(E7,B:B,0)-1))),"",IF(E6="","",IF(E7=0,MATCH("zzz",B:B,1),MATCH(E7,B:B,0)-1)))

I appreciate your kind help

Good Luck and have a nice weekend

Kind Regards,
Moti :)
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top