Index/Match/Vlookup - No Duplicates

[kaemerso]

I am trying to create a smart #.

So far I have tried using the concatenate function alongside v-lookup. As well as a variation of index/match nested within if statements.

I either get an error (tried iferror), n/a (tried ifna) or #ref , which means the range is probably wrong.

Attached is a sample worksheet:
If anyone could offer some advice that would be helpful.

[TABLE="width: 435"]
<colgroup><col></colgroup><tbody>[TR]
[TD]

Ex: Whenever 1E is selected then 1E01 is returned, whenever 1E is selected again - then 1E02 is returned (Unique Value)

1E and 1C are static whereas 01,02, etc increases each time they are selected so they stay unique.

[TABLE="width: 967"]
<colgroup><col><col span="10"></colgroup><tbody>[TR]
[TD]FOLDER ID[/TD]
[TD]01[/TD]
[TD]02[/TD]
[TD]03[/TD]
[TD]04[/TD]
[TD]05[/TD]
[TD]06[/TD]
[TD]07[/TD]
[TD]08[/TD]
[TD]09[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1E[/TD]
[TD]1E01[/TD]
[TD]1E02[/TD]
[TD]1E03[/TD]
[TD]1E04[/TD]
[TD]1E05[/TD]
[TD]1E06[/TD]
[TD]1E07[/TD]
[TD]1E08[/TD]
[TD]1E09[/TD]
[TD]1E10[/TD]
[/TR]
[TR]
[TD]1C[/TD]
[TD]1C01[/TD]
[TD]1C02[/TD]
[TD]1C03[/TD]
[TD]1C04[/TD]
[TD]1C05[/TD]
[TD]1C06[/TD]
[TD]1C07[/TD]
[TD]1C08[/TD]
[TD]1C09[/TD]
[TD]1C10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 967"]
<colgroup><col><col span="10"></colgroup><tbody>[TR]
[TD][CODE][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 268"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Selected Folder ID[/TD]
[TD]Output Should be [/TD]
[/TR]
[TR]
[TD]1E[/TD]
[TD]1E01[/TD]
[/TR]
[TR]
[TD]1E[/TD]
[TD]1E02[/TD]
[/TR]
[TR]
[TD]1E[/TD]
[TD]1E03[/TD]
[/TR]
[TR]
[TD]2C[/TD]
[TD]2C01[/TD]
[/TR]
[TR]
[TD]2C[/TD]
[TD]2C02[/TD]
[/TR]
[TR]
[TD]3E[/TD]
[TD]3E01[/TD]
[/TR]
[TR]
[TD]3C[/TD]
[TD]3C01[/TD]
[/TR]
</tbody>[/TABLE]

 

[kaemerso]

I do not have permission to post a workbook or edit my original text - this is the gist of what I am searching for though.

 

[AhoyNC]

Something like this?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	FOLDER ID	01	02	03	04	05	06	07	08	09	10
2	1E	1E01	1E02	1E03	1E04	1E05	1E06	1E07	1E08	1E09	1E10
3	1C	1C01	1C02	1C03	1C04	1C05	1C06	1C07	1C08	1C09	1C10
4	2C	2C01	2C02	2C03	2C04	2C05	2C06	2C07	2C08	2C09	2C10
5	3E	3E01	3E02	3E03	3E04	3E05	3E06	3E07	3E08	3E09	3E10
6	3C	3C01	3C02	3C03	3C04	3C05	3C06	3C07	3C08	3C09	3C10
7
8
9	FOLDER ID
10	1E	1E01
11	1E	1E02
12	1E	1E03
13	2C	2C01
14	2C	2C02
15	3E	3E01
16	3C	3C01
17	1E	1E04
Sheet

 

[kaemerso]

Something like this?

	A	B	C	D	E	F	G	H	I	J	K
1E
1C	1C01	1C02	1C03	1C04	1C05	1C06	1C07	1C08	1C09	1C10
2C	2C01	2C02	2C03	2C04	2C05	2C06	2C07	2C08	2C09	2C10
3E
3C	3C01	3C02	3C03	3C04	3C05	3C06	3C07	3C08	3C09	3C10
1E
1E
1E
2C
2C
3E
3C
1E

<colgroup><col style="width:30px; "><col style="width:80px;"><col style="width:84px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]FOLDER ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]01[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]02[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]03[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]04[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]05[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]06[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]07[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]08[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]09[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]1E01[/TD]
[TD="align: right"]1E02[/TD]
[TD="align: right"]1E03[/TD]
[TD="align: right"]1E04[/TD]
[TD="align: right"]1E05[/TD]
[TD="align: right"]1E06[/TD]
[TD="align: right"]1E07[/TD]
[TD="align: right"]1E08[/TD]
[TD="align: right"]1E09[/TD]
[TD="align: right"]1E10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]3E01[/TD]
[TD="align: right"]3E02[/TD]
[TD="align: right"]3E03[/TD]
[TD="align: right"]3E04[/TD]
[TD="align: right"]3E05[/TD]
[TD="align: right"]3E06[/TD]
[TD="align: right"]3E07[/TD]
[TD="align: right"]3E08[/TD]
[TD="align: right"]3E09[/TD]
[TD="align: right"]3E10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]FOLDER ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]1E01[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]1E02[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]1E03[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]2C01[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]2C02[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]3E01[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]3C01[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]1E04[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B10 =INDEX($B$2:$K$6,MATCH($A10,$A$2:$A$6,0),COUNTIF($A$10:A10,A10)) B11 =INDEX($B$2:$K$6,MATCH($A11,$A$2:$A$6,0),COUNTIF($A$10:A11,A11)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

PERFECT! Thank you so much. I would've never thought to use the count-if function!! I saw a couple of examples online but it just confused me even further. I have been at this since 9AM this morning.

Many thanks again!

 

[AhoyNC]

You're welcome. Here's another option that may work for you.

Excel Workbook
	A	B
1	FOLDER ID
2	1E	1E01
3	1E	1E02
4	1E	1E03
5	2C	2C01
6	2C	2C02
7	3E	3E01
8	3C	3C01
9	1E	1E04
Sheet

 

[kaemerso]

You're welcome. Here's another option that may work for you.

	A	B

<colgroup><col style="width:30px; "><col style="width:84px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]FOLDER ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]1E[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]1E01[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]1E[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]1E02[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]1E[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]1E03[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]2C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]2C01[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]2C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]2C02[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]3E[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]3E01[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]3C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]3C01[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: center"]1E[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]1E04[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =A2&TEXT(COUNTIF($A$2:A2,A2),"0#") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Works just as well!
Just for educational purposes - is the "0#" changing the numbers to a string for the text formula to work?

 

[AhoyNC]

Yes. The "0#" puts a 0 in front of the numbers 1 through 9. Without it you would get 1E1, 1E2, etc. as below.

Excel Workbook
	A	B
1	FOLDER ID
2	1E	1E1
3	1E	1E2
4	1E	1E3
5	2C	2C1
6	2C	2C2
7	3E	3E1
8	3C	3C1
9	1E	1E4
Sheet

 

[kaemerso]

Is there any way to loop this in VBA? I have no idea how to make loops in vba - each time i just confuse myself? Any help would be much appreciated! Thank you.

So far I have:

Private Sub TextBox3_Change()


TextBox5.value = WorksheetFunction.IfError(Worksheets("Sheet1"), WorksheetFunction.Index(Worksheets("Sheet1").Range("b2:d29"), WorksheetFunction.Match(Worksheets("Sheet1"), "L50", ("a2:a29"), 0, WorksheetFunction.CountIf(Worksheets("Sheet1").Range("$l$50:l50"), "L50", ""), "")))

Each time data is entered I would like that unique ID as a "confirmation" number - essentially?