Return next unique value from list based on multiple criteria

J

jozzy

New Member
Joined
May 20, 2009
Messages
21
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
 
Sort by date Sort by votes
Marcelo Branco said:
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.
Click to expand...

Marcelo, that is correct!.......(Not sure who set this file naming database up but clearly they didn't think about it) :P
If worst comes to worst I can always request that each System has it's own unique file number.

Here is a true example what is required below.

When either of the System; 'Sys45, 'Sys30' or 'E45' is input into the System column the file number needs to continue on from the previous. What were you thinking when you mentioned the addition of another column?

Worksheet_1:

[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]Example<example< em=""></example<>[/TD]
[TD]VIC[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sys45[/TD]
[/TR]
[TR]
[TD]15/07[/TD]
[TD]1/08[/TD]
[TD]2/08[/TD]
[TD]SV2001[/TD]
[TD]Example[/TD]
[TD]VIC[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sys30[/TD]
[/TR]
[TR]
[TD]15/07[/TD]
[TD]28/07[/TD]
[TD]2/08[/TD]
[TD]SV2002[/TD]
[TD]Example[/TD]
[TD]VIC[/TD]
[TD][/TD]
[TD][/TD]
[TD]E45[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SV2003[/TD]
[TD]Example[/TD]
[TD]VIC[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sys45[/TD]
[/TR]
[TR]
[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]
[/TR]
</tbody>[/TABLE]


Worksheet_2:

[TABLE="class: grid, width: 1458"]
<tbody>[TR]
[TD]System Name:[/TD]
[TD]Sys45[/TD]
[TD]Sys45[/TD]
[TD]Sys45[/TD]
[TD]Sys45[/TD]
[TD]Sys45[/TD]
[TD]Sys45[/TD]
[TD]Sys45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]System Name:[/TD]
[TD]Sys30[/TD]
[TD]Sys30[/TD]
[TD]Sys30[/TD]
[TD]Sys30[/TD]
[TD]Sys30[/TD]
[TD]Sys30[/TD]
[TD]Sys30[/TD]
[TD]I70[/TD]
[TD]I70[/TD]
[TD]I70[/TD]
[TD]I70[/TD]
[TD]I70[/TD]
[TD]I70[/TD]
[TD]I70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]System Name:[/TD]
[TD]E45[/TD]
[TD]E45[/TD]
[TD]E45[/TD]
[TD]E45[/TD]
[TD]E45[/TD]
[TD]E45[/TD]
[TD]E45[/TD]
[TD]I90[/TD]
[TD]I90[/TD]
[TD]I90[/TD]
[TD]I90[/TD]
[TD]I90[/TD]
[TD]I90[/TD]
[TD]I90[/TD]
[TD]Sys70[/TD]
[TD]Sys70[/TD]
[TD]Sys70[/TD]
[TD]Sys70[/TD]
[TD]Sys70[/TD]
[TD]Sys70[/TD]
[TD]Sys70[/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]
</tbody>[/TABLE]
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I was expecting something like this...

A possible solution

1. Keep the big table exactly as your post #1 (same headers in row 2, i.e., System 1, System1....,System 2, System 2....)


2. Create a table like this

[TABLE="class: grid, width: 200"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]FileName
[/TD]
[TD="width: 64, bgcolor: transparent"]Category
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sys45
[/TD]
[TD="bgcolor: transparent"]System 1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sys30
[/TD]
[TD="bgcolor: transparent"]System 1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E45
[/TD]
[TD="bgcolor: transparent"]System 1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]I70
[/TD]
[TD="bgcolor: transparent"]System 2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]I90
[/TD]
[TD="bgcolor: transparent"]System 2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sys70
[/TD]
[TD="bgcolor: transparent"]System 3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]...
[/TD]
[TD="bgcolor: transparent"]...
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]...
[/TD]
[TD="bgcolor: transparent"]...
[/TD]
[/TR]
</TBODY>[/TABLE]

Name the range, say, Files

**This will you give flexibility to add new File Names and new Categories, if needed***


3. In worksheet1 create a new column between the columns Job No. and System
Insert a VLOOKUP formula to get the Category of the file. Something like (assuming System will be column J)

=VLOOKUP(J2,Files,2,0)


Then you can use the first formula i suggested in #2 - maybe you need to adjust the ranges (columns)

Hope this helps.

M.
 
Last edited:
Upvote 0
correction: i meant

1. Keep the big table exactly as your post #1 (same headers in row 1, i.e., System 1, System1....,System 2, System 2....)

Not row 2

M.
 
Upvote 0
Marcelo Branco said:
correction: i meant

1. Keep the big table exactly as your post #1 (same headers in row 1, i.e., System 1, System1....,System 2, System 2....)

Not row 2

M.
Click to expand...


Thank you yet again!

This makes sense :)
............I will try this out when I get a spare minute later on, shall let you know the results!


Cheers!
 
Upvote 0
Marcelo Branco said:
1. Keep the big table exactly as your post #1 (same headers in row 2, i.e., System 1, System1....,System 2, System 2....)


2. Create a table like this

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]FileName[/TD]
[TD="width: 64, bgcolor: transparent"]Category[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sys45[/TD]
[TD="bgcolor: transparent"]System 1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sys30[/TD]
[TD="bgcolor: transparent"]System 1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E45[/TD]
[TD="bgcolor: transparent"]System 1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]I70[/TD]
[TD="bgcolor: transparent"]System 2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]I90[/TD]
[TD="bgcolor: transparent"]System 2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sys70[/TD]
[TD="bgcolor: transparent"]System 3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]...[/TD]
[TD="bgcolor: transparent"]...[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]...[/TD]
[TD="bgcolor: transparent"]...[/TD]
[/TR]
</tbody>[/TABLE]

Name the range, say, Files

**This will you give flexibility to add new File Names and new Categories, if needed***

3. In worksheet1 create a new column between the columns Job No. and System
Insert a VLOOKUP formula to get the Category of the file. Something like (assuming System will be column J)

=VLOOKUP(J2,Files,2,0)

Then you can use the first formula i suggested in #2 - maybe you need to adjust the ranges (columns)
M.
Click to expand...


Perfect solution......works a treat! :D

Many thanks again Marcelo.


Cheers!

Sean
 
Upvote 0
You must log in or register to reply here.

Similar threads

BrettOlbrys1
  • Solved
Count Unique Values Based on Multiple Criteria
Replies
6
Views
467
BrettOlbrys1
BrettOlbrys1
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
901
paulkc
P
M
  • Solved
Returning results based on multiple criteria
Replies
5
Views
497
DanteAmor
DanteAmor
Z
  • Question Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
401
zero269
Z
M
  • Question Question
return multiple matching values into one cell based on multiple criteria in Excel
Replies
3
Views
446
MMM_84
M

Forum statistics

Threads
1,223,958
Messages
6,175,633
Members
452,661
Latest member
Nonhle

Share this page

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