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.

Date RevisedEnteredAssemblyEx-WorksFILE No.JOB NAMEStateOrder #Job No.System
15/0731/072/08TD1Insert job name hereQLDSystem 3
15/071/082/08UC2000Insert job name hereNSWSystem 2
15/0728/072/08TD2Insert job name hereQLDSystem 3
#
#
#

<tbody>
</tbody>


System Type:System 1System 1System 1System 1System 1System 1System 1System 2System 2System 2System 2System 2System 2System 2System 3System 3System 3System 3System 3System 3System 3
State:VICACTNSWQLDSAWAEXPVICACTNSWQLDSAWAEXPVICACTNSWQLDSAWAEXP
File No.SV2000SE2000SC2000SD2000SG2000SF2000SX2000UV2000UE2000UC2000UD2000UG2000UF2000UX2000TV1TE1TC1TD1TG1TF1TX1
SV2001SE2001SC2001SD2001SG2001SF2001SX2001UV2001UE2001UC2001UD2001UG2001UF2001UX2001TV2TE2TC2TD2TG2TF2TX2
SV2002SE2002SC2002SD2002SG2002SF2002SX2002UV2002UE2002UC2002UD2002UG2002UF2002UX2002TV3TE3TC3TD3TG3TF3TX3
SV2003SE2003SC2003SD2003SG2003SF2003SX2003UV2003UE2003UC2003UD2003UG2003UF2003UX2003TV4TE4TC4TD4TG4TF4TX4
SV2004SE2004SC2004SD2004SG2004SF2004SX2004UV2004UE2004UC2004UD2004UG2004UF2004UX2004TV5TE5TC5TD5TG5TF5TX5
SV2005SE2005SC2005SD2005SG2005SF2005SX2005UV2005UE2005UC2005UD2005UG2005UF2005UX2005TV6TE6TC6TD6TG6TF6TX6
SV2006SE2006SC2006SD2006SG2006SF2006SX2006UV2006UE2006UC2006UD2006UG2006UF2006UX2006TV7TE7TC7TD7TG7TF7TX7
SV2007SE2007SC2007SD2007SG2007SF2007SX2007UV2007UE2007UC2007UD2007UG2007UF2007UX2007TV8TE8TC8TD8TG8TF8TX8
SV2008SE2008SC2008SD2008SG2008SF2008SX2008UV2008UE2008UC2008UD2008UG2008UF2008UX2008TV9TE9TC9TD9TG9TF9TX9
SV2009SE2009SC2009SD2009SG2009SF2009SX2009UV2009UE2009UC2009UD2009UG2009UF2009UX2009TV10TE10TC10TD10TG10TF10TX10

<tbody>
</tbody>


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

Cheers!



Excel 2003 / Windows XP
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
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.
 
Upvote 0
Marcelo........amazing work there! :biggrin: 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!
 
Upvote 0
jozzy said:
Marcelo........amazing work there! :biggrin: 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!
Click to expand...

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.
 
Upvote 0
Marcelo Branco said:
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
Click to expand...

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.


EnteredAssemblyEx-WorksFILE No.JOB NAMEStateOrder #Job No.System
15/0731/072/08SV2000Insert job name hereVICSystem 1
15/071/082/08SV2001Insert job name hereVICSystem 1
15/0728/072/08SV2002Example OnlyVICSystem 1.1
SV2003Example OnlyVICSystem 1.2
SV2004Insert job name hereVICSystem 1
###

<tbody>
</tbody>



System Type:System 1, System 1.1, System 1.2System 1, System 1.1, System 1.2System 1, System 1.1, System 1.2System 1, System 1.1, System 1.2System 1, System 1.1, System 1.2System 1, System 1.1, System 1.2System 1, System 1.1, System 1.2System 2, System 2.1, System 2.2System 2, System 2.1, System 2.2System 2, System 2.1, System 2.2System 2, System 2.1, System 2.2System 2, System 2.1, System 2.2System 2, System 2.1, System 2.2System 2, System 2.1, System 2.2
State:VICACTNSWQLDSAWAEXPVICACTNSWQLDSAWAEXP
File No.SV2000SE2000SC2000SD2000SG2000SF2000SX2000UV2000UE2000UC2000UD2000UG2000UF2000UX2000
SV2001SE2001SC2001SD2001SG2001SF2001SX2001UV2001UE2001UC2001UD2001UG2001UF2001UX2001
SV2002SE2002SC2002SD2002SG2002SF2002SX2002UV2002UE2002UC2002UD2002UG2002UF2002UX2002
SV2003SE2003SC2003SD2003SG2003SF2003SX2003UV2003UE2003UC2003UD2003UG2003UF2003UX2003
SV2004SE2004SC2004SD2004SG2004SF2004SX2004UV2004UE2004UC2004UD2004UG2004UF2004UX2004
SV2005SE2005SC2005SD2005SG2005SF2005SX2005UV2005UE2005UC2005UD2005UG2005UF2005UX2005
SV2006SE2006SC2006SD2006SG2006SF2006SX2006UV2006UE2006UC2006UD2006UG2006UF2006UX2006
SV2007SE2007SC2007SD2007SG2007SF2007SX2007UV2007UE2007UC2007UD2007UG2007UF2007UX2007

<tbody>
</tbody>


Cheers!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Marcelo Branco said:
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.
Click to expand...

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!
 
Upvote 0
jozzy said:
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!
Click to expand...

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 ...:confused:

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

M.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Returning results based on multiple criteria
Replies
5
Views
280
DanteAmor
DanteAmor
Z
  • Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
179
zero269
Z
M
  • Question
return multiple matching values into one cell based on multiple criteria in Excel
Replies
3
Views
282
MMM_84
M
W
  • Solved
TEXTJOIN return UNIQUE values with INDEXMATCH multiple criteria
Replies
2
Views
672
WhimsicalRaptor87
W
J
  • Solved
How to aggregate data based on multiple criteria?
Replies
8
Views
538
Jurez
J

Forum statistics

Threads
1,214,950
Messages
6,122,436
Members
449,083
Latest member
Ava19

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