Return next unique value from list based on multiple criteria

[jozzy]

G'Day Excel people's,

Background:
What I have is an Excel sheet set up as a monthly schedule. Currently a file number is allocated to each job manually in a separate Excel workbook and then this is manually inputted onto the monthly schedule. This is a cumbersome timely process (and who here loves typing out the same spiel multiple times!!!).
File numbers are based on two criteria; State order is placed from (manual entry) and System name (manual entry)

Task:
What I require is when I enter the two criteria; State and System Name I require Excel to return the next unique file number to a job from a list based on meeting the two criteria.

Hopefully I have made this easier by attaching a sample workbook (not sure how to attach so provided sample below instead) with the schedule and lists of unique file numbers. My guess is that what I am looking for is some form of Lookup, but I am really unsure how I can make Excel assign the next unique number from a list if the number above it has already been used.

[TABLE="class: grid, width: 745"]
<tbody>[TR]
[TD]Date Revised[/TD]
[TD]Entered[/TD]
[TD]Assembly[/TD]
[TD]Ex-Works[/TD]
[TD]FILE No.[/TD]
[TD]JOB NAME[/TD]
[TD]State[/TD]
[TD]Order #[/TD]
[TD]Job No.[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]15/07[/TD]
[TD]31/07[/TD]
[TD]2/08[/TD]
[TD]TD1[/TD]
[TD]Insert job name here[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD][/TD]
[TD]System 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]15/07[/TD]
[TD]1/08[/TD]
[TD]2/08[/TD]
[TD]UC2000[/TD]
[TD]Insert job name here[/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD][/TD]
[TD]System 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]15/07[/TD]
[TD]28/07[/TD]
[TD]2/08[/TD]
[TD]TD2[/TD]
[TD]Insert job name here[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD][/TD]
[TD]System 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 1458"]
<tbody>[TR]
[TD]System Type:[/TD]
[TD]System 1[/TD]
[TD]System 1[/TD]
[TD]System 1[/TD]
[TD]System 1[/TD]
[TD]System 1[/TD]
[TD]System 1[/TD]
[TD]System 1[/TD]
[TD]System 2[/TD]
[TD]System 2[/TD]
[TD]System 2[/TD]
[TD]System 2[/TD]
[TD]System 2[/TD]
[TD]System 2[/TD]
[TD]System 2[/TD]
[TD]System 3[/TD]
[TD]System 3[/TD]
[TD]System 3[/TD]
[TD]System 3[/TD]
[TD]System 3[/TD]
[TD]System 3[/TD]
[TD]System 3[/TD]
[/TR]
[TR]
[TD]State:[/TD]
[TD]VIC[/TD]
[TD]ACT[/TD]
[TD]NSW[/TD]
[TD]QLD[/TD]
[TD]SA[/TD]
[TD]WA[/TD]
[TD]EXP[/TD]
[TD]VIC[/TD]
[TD]ACT[/TD]
[TD]NSW[/TD]
[TD]QLD[/TD]
[TD]SA[/TD]
[TD]WA[/TD]
[TD]EXP[/TD]
[TD]VIC[/TD]
[TD]ACT[/TD]
[TD]NSW[/TD]
[TD]QLD[/TD]
[TD]SA[/TD]
[TD]WA[/TD]
[TD]EXP[/TD]
[/TR]
[TR]
[TD]File No.[/TD]
[TD]SV2000[/TD]
[TD]SE2000[/TD]
[TD]SC2000[/TD]
[TD]SD2000[/TD]
[TD]SG2000[/TD]
[TD]SF2000[/TD]
[TD]SX2000[/TD]
[TD]UV2000[/TD]
[TD]UE2000[/TD]
[TD]UC2000[/TD]
[TD]UD2000[/TD]
[TD]UG2000[/TD]
[TD]UF2000[/TD]
[TD]UX2000[/TD]
[TD]TV1[/TD]
[TD]TE1[/TD]
[TD]TC1[/TD]
[TD]TD1[/TD]
[TD]TG1[/TD]
[TD]TF1[/TD]
[TD]TX1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2001[/TD]
[TD]SE2001[/TD]
[TD]SC2001[/TD]
[TD]SD2001[/TD]
[TD]SG2001[/TD]
[TD]SF2001[/TD]
[TD]SX2001[/TD]
[TD]UV2001[/TD]
[TD]UE2001[/TD]
[TD]UC2001[/TD]
[TD]UD2001[/TD]
[TD]UG2001[/TD]
[TD]UF2001[/TD]
[TD]UX2001[/TD]
[TD]TV2[/TD]
[TD]TE2[/TD]
[TD]TC2[/TD]
[TD]TD2[/TD]
[TD]TG2[/TD]
[TD]TF2[/TD]
[TD]TX2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2002[/TD]
[TD]SE2002[/TD]
[TD]SC2002[/TD]
[TD]SD2002[/TD]
[TD]SG2002[/TD]
[TD]SF2002[/TD]
[TD]SX2002[/TD]
[TD]UV2002[/TD]
[TD]UE2002[/TD]
[TD]UC2002[/TD]
[TD]UD2002[/TD]
[TD]UG2002[/TD]
[TD]UF2002[/TD]
[TD]UX2002[/TD]
[TD]TV3[/TD]
[TD]TE3[/TD]
[TD]TC3[/TD]
[TD]TD3[/TD]
[TD]TG3[/TD]
[TD]TF3[/TD]
[TD]TX3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2003[/TD]
[TD]SE2003[/TD]
[TD]SC2003[/TD]
[TD]SD2003[/TD]
[TD]SG2003[/TD]
[TD]SF2003[/TD]
[TD]SX2003[/TD]
[TD]UV2003[/TD]
[TD]UE2003[/TD]
[TD]UC2003[/TD]
[TD]UD2003[/TD]
[TD]UG2003[/TD]
[TD]UF2003[/TD]
[TD]UX2003[/TD]
[TD]TV4[/TD]
[TD]TE4[/TD]
[TD]TC4[/TD]
[TD]TD4[/TD]
[TD]TG4[/TD]
[TD]TF4[/TD]
[TD]TX4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2004[/TD]
[TD]SE2004[/TD]
[TD]SC2004[/TD]
[TD]SD2004[/TD]
[TD]SG2004[/TD]
[TD]SF2004[/TD]
[TD]SX2004[/TD]
[TD]UV2004[/TD]
[TD]UE2004[/TD]
[TD]UC2004[/TD]
[TD]UD2004[/TD]
[TD]UG2004[/TD]
[TD]UF2004[/TD]
[TD]UX2004[/TD]
[TD]TV5[/TD]
[TD]TE5[/TD]
[TD]TC5[/TD]
[TD]TD5[/TD]
[TD]TG5[/TD]
[TD]TF5[/TD]
[TD]TX5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2005[/TD]
[TD]SE2005[/TD]
[TD]SC2005[/TD]
[TD]SD2005[/TD]
[TD]SG2005[/TD]
[TD]SF2005[/TD]
[TD]SX2005[/TD]
[TD]UV2005[/TD]
[TD]UE2005[/TD]
[TD]UC2005[/TD]
[TD]UD2005[/TD]
[TD]UG2005[/TD]
[TD]UF2005[/TD]
[TD]UX2005[/TD]
[TD]TV6[/TD]
[TD]TE6[/TD]
[TD]TC6[/TD]
[TD]TD6[/TD]
[TD]TG6[/TD]
[TD]TF6[/TD]
[TD]TX6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2006[/TD]
[TD]SE2006[/TD]
[TD]SC2006[/TD]
[TD]SD2006[/TD]
[TD]SG2006[/TD]
[TD]SF2006[/TD]
[TD]SX2006[/TD]
[TD]UV2006[/TD]
[TD]UE2006[/TD]
[TD]UC2006[/TD]
[TD]UD2006[/TD]
[TD]UG2006[/TD]
[TD]UF2006[/TD]
[TD]UX2006[/TD]
[TD]TV7[/TD]
[TD]TE7[/TD]
[TD]TC7[/TD]
[TD]TD7[/TD]
[TD]TG7[/TD]
[TD]TF7[/TD]
[TD]TX7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2007[/TD]
[TD]SE2007[/TD]
[TD]SC2007[/TD]
[TD]SD2007[/TD]
[TD]SG2007[/TD]
[TD]SF2007[/TD]
[TD]SX2007[/TD]
[TD]UV2007[/TD]
[TD]UE2007[/TD]
[TD]UC2007[/TD]
[TD]UD2007[/TD]
[TD]UG2007[/TD]
[TD]UF2007[/TD]
[TD]UX2007[/TD]
[TD]TV8[/TD]
[TD]TE8[/TD]
[TD]TC8[/TD]
[TD]TD8[/TD]
[TD]TG8[/TD]
[TD]TF8[/TD]
[TD]TX8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2008[/TD]
[TD]SE2008[/TD]
[TD]SC2008[/TD]
[TD]SD2008[/TD]
[TD]SG2008[/TD]
[TD]SF2008[/TD]
[TD]SX2008[/TD]
[TD]UV2008[/TD]
[TD]UE2008[/TD]
[TD]UC2008[/TD]
[TD]UD2008[/TD]
[TD]UG2008[/TD]
[TD]UF2008[/TD]
[TD]UX2008[/TD]
[TD]TV9[/TD]
[TD]TE9[/TD]
[TD]TC9[/TD]
[TD]TD9[/TD]
[TD]TG9[/TD]
[TD]TF9[/TD]
[TD]TX9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2009[/TD]
[TD]SE2009[/TD]
[TD]SC2009[/TD]
[TD]SD2009[/TD]
[TD]SG2009[/TD]
[TD]SF2009[/TD]
[TD]SX2009[/TD]
[TD]UV2009[/TD]
[TD]UE2009[/TD]
[TD]UC2009[/TD]
[TD]UD2009[/TD]
[TD]UG2009[/TD]
[TD]UF2009[/TD]
[TD]UX2009[/TD]
[TD]TV10[/TD]
[TD]TE10[/TD]
[TD]TC10[/TD]
[TD]TD10[/TD]
[TD]TG10[/TD]
[TD]TF10[/TD]
[TD]TX10[/TD]
[/TR]
</tbody>[/TABLE]


Should you require more info from me please don't hesitate to ask!

Cheers!



Excel 2003 / Windows XP

 

[Marcelo Branco]

A possible solution

Create a named range, say, Table 1 doing:
Select your big table (that at the bottom of your post)
Type in the Name Box (beside the formula bar)
Table1
hit Enter

Assuming your first table at columns A:J, headers in row 1, put this array formula in E2

=IF(AND(G2<>"",J2<>""),INDEX(Table1,3+SUMPRODUCT(--($G$1:G1=G2),--($J$1:J1=J2)),MATCH(J2&"|"&G2,INDEX(Table1,1,0)&"|"&INDEX(Table1,2,0),0)),"")

confirmed with Ctrl+Shift+Enter simultaneously, not just Enter

copy down

M.

 

[jozzy]

Marcelo........amazing work there! Formula works perfectly thank you!

Now I am one for learning so just trying to get my head around what each part is actually doing. Feel free to explain if you have the time.

Thanks again for providing a quick response and working formula!

 

[Marcelo Branco]

Marcelo........amazing work there! Formula works perfectly thank you!

Now I am one for learning so just trying to get my head around what each part is actually doing. Feel free to explain if you have the time.

Thanks again for providing a quick response and working formula!

You are very welcome and thanks for the feedback!

1. I think this is the key part of the formula

MATCH(J2&"|"&G2,INDEX(Table1,1,0)&"|"&INDEX(Table1,2,0),0)

The part in blue concatenates the first row of the big table with the second row producing a horizontal array like

{"System Type|State:","System 1|VIC","System 1|ACT",..................................}

Then you can match the concatenation J2&"|"&G2 against this array to find the proper column.


2. SUMPRODUCT(--($G$1:G1=G2),--($J$1:J1=J2)) counts how many occurrences of the combination State - System, of the specific row, there are in the rows above.

We add 3 because the first File No. is in the third row of the big table (Table1). So if SUMPRODUCT returns 0 (none combination State-System in the rows above) the formula gets the first File No. at row 3 of Table1. If it returns 1, gets the second File No. at row 4; and so on.

Try the tool Formulas > Evaluate formula to see, step by step, exactly what the formula does.

Good luck and don't hesitate to ask anything.

M.

 

[jozzy]

Create a named range, say, Table 1 doing:
Select your big table (that at the bottom of your post)
Type in the Name Box (beside the formula bar)
Table1
hit Enter

Assuming your first table at columns A:J, headers in row 1, put this array formula in E2

=IF(AND(G2<>"",J2<>""),INDEX(Table1,3+SUMPRODUCT(--($G$1:G1=G2),--($J$1:J1=J2)),MATCH(J2&"|"&G2,INDEX(Table1,1,0)&"|"&INDEX(Table1,2,0),0)),"")

confirmed with Ctrl+Shift+Enter simultaneously, not just Enter

This works perfectly when I want to look up a simple setup such as 'VIC' & 'System 1' returning the result of 'SV2000' from the above table.

Now what if I were to look up 'System 1, System 1.1, System 1.2, System 2, System 2.1......etc, etc.

System 1 and System 1.1 etc are required to use the same File No. formatting and is required to continue the numbering sequence meaning it's not as easy as placing 'System 1.1' into separate column (or is it?). So rather than use Match could I possibly use Search?

Below is an example of what I would require.


[TABLE="class: grid, width: 681"]
<tbody>[TR]
[TD]Entered[/TD]
[TD]Assembly[/TD]
[TD]Ex-Works[/TD]
[TD]FILE No.[/TD]
[TD]JOB NAME[/TD]
[TD]State[/TD]
[TD]Order #[/TD]
[TD]Job No.[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD]15/07[/TD]
[TD]31/07[/TD]
[TD]2/08[/TD]
[TD]SV2000[/TD]
[TD]Insert job name here[/TD]
[TD]VIC[/TD]
[TD][/TD]
[TD][/TD]
[TD]System 1[/TD]
[/TR]
[TR]
[TD]15/07[/TD]
[TD]1/08[/TD]
[TD]2/08[/TD]
[TD]SV2001[/TD]
[TD]Insert job name here[/TD]
[TD]VIC[/TD]
[TD][/TD]
[TD][/TD]
[TD]System 1[/TD]
[/TR]
[TR]
[TD]15/07[/TD]
[TD]28/07[/TD]
[TD]2/08[/TD]
[TD]SV2002[/TD]
[TD]Example Only[/TD]
[TD]VIC[/TD]
[TD][/TD]
[TD][/TD]
[TD]System 1.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SV2003[/TD]
[TD]Example Only[/TD]
[TD]VIC[/TD]
[TD][/TD]
[TD][/TD]
[TD]System 1.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SV2004[/TD]
[TD]Insert job name here[/TD]
[TD]VIC[/TD]
[TD][/TD]
[TD][/TD]
[TD]System 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#[/TD]
[TD][/TD]
[TD]#[/TD]
[TD][/TD]
[TD][/TD]
[TD]#[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: grid, width: 1010"]
<tbody>[TR]
[TD]System Type:[/TD]
[TD]System 1, System 1.1, System 1.2[/TD]
[TD]System 1, System 1.1, System 1.2[/TD]
[TD]System 1, System 1.1, System 1.2[/TD]
[TD]System 1, System 1.1, System 1.2[/TD]
[TD]System 1, System 1.1, System 1.2[/TD]
[TD]System 1, System 1.1, System 1.2[/TD]
[TD]System 1, System 1.1, System 1.2[/TD]
[TD]System 2, System 2.1, System 2.2[/TD]
[TD]System 2, System 2.1, System 2.2[/TD]
[TD]System 2, System 2.1, System 2.2[/TD]
[TD]System 2, System 2.1, System 2.2[/TD]
[TD]System 2, System 2.1, System 2.2[/TD]
[TD]System 2, System 2.1, System 2.2[/TD]
[TD]System 2, System 2.1, System 2.2[/TD]
[/TR]
[TR]
[TD]State:[/TD]
[TD]VIC[/TD]
[TD]ACT[/TD]
[TD]NSW[/TD]
[TD]QLD[/TD]
[TD]SA[/TD]
[TD]WA[/TD]
[TD]EXP[/TD]
[TD]VIC[/TD]
[TD]ACT[/TD]
[TD]NSW[/TD]
[TD]QLD[/TD]
[TD]SA[/TD]
[TD]WA[/TD]
[TD]EXP[/TD]
[/TR]
[TR]
[TD]File No.[/TD]
[TD]SV2000[/TD]
[TD]SE2000[/TD]
[TD]SC2000[/TD]
[TD]SD2000[/TD]
[TD]SG2000[/TD]
[TD]SF2000[/TD]
[TD]SX2000[/TD]
[TD]UV2000[/TD]
[TD]UE2000[/TD]
[TD]UC2000[/TD]
[TD]UD2000[/TD]
[TD]UG2000[/TD]
[TD]UF2000[/TD]
[TD]UX2000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2001[/TD]
[TD]SE2001[/TD]
[TD]SC2001[/TD]
[TD]SD2001[/TD]
[TD]SG2001[/TD]
[TD]SF2001[/TD]
[TD]SX2001[/TD]
[TD]UV2001[/TD]
[TD]UE2001[/TD]
[TD]UC2001[/TD]
[TD]UD2001[/TD]
[TD]UG2001[/TD]
[TD]UF2001[/TD]
[TD]UX2001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2002[/TD]
[TD]SE2002[/TD]
[TD]SC2002[/TD]
[TD]SD2002[/TD]
[TD]SG2002[/TD]
[TD]SF2002[/TD]
[TD]SX2002[/TD]
[TD]UV2002[/TD]
[TD]UE2002[/TD]
[TD]UC2002[/TD]
[TD]UD2002[/TD]
[TD]UG2002[/TD]
[TD]UF2002[/TD]
[TD]UX2002[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2003[/TD]
[TD]SE2003[/TD]
[TD]SC2003[/TD]
[TD]SD2003[/TD]
[TD]SG2003[/TD]
[TD]SF2003[/TD]
[TD]SX2003[/TD]
[TD]UV2003[/TD]
[TD]UE2003[/TD]
[TD]UC2003[/TD]
[TD]UD2003[/TD]
[TD]UG2003[/TD]
[TD]UF2003[/TD]
[TD]UX2003[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2004[/TD]
[TD]SE2004[/TD]
[TD]SC2004[/TD]
[TD]SD2004[/TD]
[TD]SG2004[/TD]
[TD]SF2004[/TD]
[TD]SX2004[/TD]
[TD]UV2004[/TD]
[TD]UE2004[/TD]
[TD]UC2004[/TD]
[TD]UD2004[/TD]
[TD]UG2004[/TD]
[TD]UF2004[/TD]
[TD]UX2004[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2005[/TD]
[TD]SE2005[/TD]
[TD]SC2005[/TD]
[TD]SD2005[/TD]
[TD]SG2005[/TD]
[TD]SF2005[/TD]
[TD]SX2005[/TD]
[TD]UV2005[/TD]
[TD]UE2005[/TD]
[TD]UC2005[/TD]
[TD]UD2005[/TD]
[TD]UG2005[/TD]
[TD]UF2005[/TD]
[TD]UX2005[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2006[/TD]
[TD]SE2006[/TD]
[TD]SC2006[/TD]
[TD]SD2006[/TD]
[TD]SG2006[/TD]
[TD]SF2006[/TD]
[TD]SX2006[/TD]
[TD]UV2006[/TD]
[TD]UE2006[/TD]
[TD]UC2006[/TD]
[TD]UD2006[/TD]
[TD]UG2006[/TD]
[TD]UF2006[/TD]
[TD]UX2006[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SV2007[/TD]
[TD]SE2007[/TD]
[TD]SC2007[/TD]
[TD]SD2007[/TD]
[TD]SG2007[/TD]
[TD]SF2007[/TD]
[TD]SX2007[/TD]
[TD]UV2007[/TD]
[TD]UE2007[/TD]
[TD]UC2007[/TD]
[TD]UD2007[/TD]
[TD]UG2007[/TD]
[TD]UF2007[/TD]
[TD]UX2007[/TD]
[/TR]
</tbody>[/TABLE]


Cheers!

 

[Marcelo Branco]

I would not change the headers of the big table. Keep them exactly as they are in your post #1.

Then with a small change in the formula i think (hope ) that everything will work fine.

New formula
=IF(AND(G2<>"",J2<>""),INDEX(Table1,3+SUMPRODUCT(--($G$1:G1=G2),--($J$1:J1=J2)),MATCH(LEFT(J2,8)&"|"&G2,INDEX(Table1,1,0)&"|"&INDEX(Table1,2,0),0)),"")

M.

 

[Marcelo Branco]

oops.. the formula needs another change

=IF(AND(G2<>"",J2<>""),INDEX(Table1,3+SUMPRODUCT(--($G$1:G1=G2),--(LEFT($J$1:J1,8)=LEFT(J2,8))),MATCH(LEFT(J2,8)&"|"&G2,INDEX(Table1,1,0)&"|"&INDEX(Table1,2,0),0)),"")

M.

 

[jozzy]

oops.. the formula needs another change

=IF(AND(G2<>"",J2<>""),INDEX(Table1,3+SUMPRODUCT(--($G$1:G1=G2),--(LEFT($J$1:J1,8)=LEFT(J2,8))),MATCH(LEFT(J2,8)&"|"&G2,INDEX(Table1,1,0)&"|"&INDEX(Table1,2,0),0)),"")

M.

This works great, however my System names I provided earlier were just examples and the names of the actual system varies. I do see what you have done with Left function however in this instance I cannot use this.
For example in replacement of the System 1 name I would have the following names 'Sys45', 'Sys30' and 'E45' - these three System names all associate with the 'System 1' File names.

I hope I explained above clearly enough for you to understand (let me know if not). Could a Search function be incorporated somehow?

Cheers Again!

 

[Marcelo Branco]

This works great, however my System names I provided earlier were just examples and the names of the actual system varies. I do see what you have done with Left function however in this instance I cannot use this.
For example in replacement of the System 1 name I would have the following names 'Sys45', 'Sys30' and 'E45' - these three System names all associate with the 'System 1' File names.

I hope I explained above clearly enough for you to understand (let me know if not). Could a Search function be incorporated somehow?

Cheers Again!

Wow...this is a different problem.
To be honest i'm a little bit lost...
I did not understand how your data (big table) is and what system names are possible ...

Please, try to post your data (the big table) with your real situation.
Simplifications can sometimes confuse more than help ...

M.

 

[Marcelo Branco]

Are you saying that for each system Category (System 1, System 2, System 3 etc, etc) there is a list of associates system names?

Is this?

If so, i *think* we can achieve a solution with an extra column...

M.