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.

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]

<colgroup><col></colgroup><tbody>

 

[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
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

<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>
</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
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

<colgroup><col style="width:30px; "><col style="width:84px;"><col style="width:64px;"></colgroup><tbody>
</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?