Finding the Next Match (text)

[yappa888]

Hi guys,

I feel I've been going around in circles trying to figure this one out... here's my problem...

I've got 2 sheets. Sheet 1 is where I am inputting all the products I'm producing and giving them serial numbers, if I produce the same part twice the name of the part is the same but the serial number changes. It looks something like this...
Sheet 1:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Part Produced
[/TD]
[TD]Serial Number
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Handle
[/TD]
[TD]1001
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Seat
[/TD]
[TD]2000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Handle
[/TD]
[TD]1002
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Handle
[/TD]
[TD]1003
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


While Sheet 1 is constantly been filled with the parts being made with their individual serial numbers. Sheet 2 is constantly being filled with the incoming orders, But only the A Column of Sheet 2 is being changed... as an order comes in I simply type into sheet 2 what product the customer is looking for.

The problem I am having is that as I write in that another "handle" for example as been ordered into sheet 2, my normal MATCH forumla is bringing up the same Serial Number "1001" over and over again. Really I'm wanting the "B" column to update to the next available serial number, so for example "1002"

Sheet 2... as it's coming out right now (wrong)...
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Part Ordered By Customer
[/TD]
[TD]Serial Number
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Handle
[/TD]
[TD]1001
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Handle
[/TD]
[TD]1001
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Seat
[/TD]
[TD]2000
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Handle
[/TD]
[TD]1001
[/TD]
[/TR]
</tbody>[/TABLE]

The formula will need to go into Sheet 2 'B' Column, and I'd like it to come out looking like this...
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Part Ordered By Customer
[/TD]
[TD]Serial Number
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Handle
[/TD]
[TD]1001
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Handle
[/TD]
[TD]1002
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Seat
[/TD]
[TD]2000
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Handle
[/TD]
[TD]1003
[/TD]
[/TR]
</tbody>[/TABLE]


Hopefully that wasn't too confusing, and hopefully someone could suggest a way of doing this without using VBA? If i can just stick to simple worksheet formulas that would be amazing!

Thanks!!!

 

[rossa]

As you fill Sheet2 are you working off the First In First Out basis?

If you insert a column to the left of the products in Sheet1 and enter the following formula "=B2&COUNTIF($B$2:B2,B2)" you will end up with this (basically the product name and the number of times it has appeared).


A B C[TABLE="width: 223"]
<tbody>[TR]
[TD]Handle1[/TD]
[TD]Handle[/TD]
[TD="align: right"]1001[/TD]
[/TR]
[TR]
[TD]Seat1[/TD]
[TD]Seat[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD]Handle2[/TD]
[TD]Handle[/TD]
[TD="align: right"]1002[/TD]
[/TR]
[TR]
[TD]Handle3[/TD]
[TD]Handle[/TD]
[TD="align: right"]1003[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]


This should work in Sheet2.
INDEX(Sheet1!$C$2:$C$5,MATCH(A2&COUNTIF($A$2:A2,A2),Sheet1!$A$2:$A$5,0))

However things like returned items might throw a spanner in the works.

 

[yappa888]

Thanks so much!!! This actually worked great! But i can see what you mean by returned goods coming back into the system possibly throwing things out... I'll have a think about this.

One other question though. At the moment when i run that formula down the rest of my list. If I havent yet put in a product for a customer then the value "0" is coming up in every box after. If there a simple way to just make it blank instead of the 0.

Beginner questions I'm sure but thanks anyways!

 

[rossa]

This works, but be aware of how far you have it copied down.
IF(B2="","",B2&COUNTIF($B$2:B2,B2))

 

[yappa888]

Brilliant!... Haha I hate to do this. But one more question...

using the code before...
INDEX(Sheet1!$C$2:$C$5,MATCH(A2&COUNTIF($A$2:A2,A2),Sheet1!$A$2:$A$5,0))

for any cells that aren't coming up with a value at the moment it's coming up with #N/A . Is there a way to change those to showing nothing as well? Similar code to the one above? I gave it a quick try but couldnt quite figure it out.

 

[pleeseemailme]

Yappa888,

This page has proven invaluable to me in looking up and returning values where MATCH usually fails: How to return multiple values using vlookup in excel | Get Digital Help - Microsoft Excel resource
Please note that this formula is what's known as an array formula so if you might see a slow down in your calculation time if you are running many, many rows at a time. Let me know if you have any questions.

 

[rossa]

No problem.
IFERROR(INDEX(Sheet1!$C$2:$C$5,MATCH(A2&COUNTIF($A$2:A2,A2),Sheet1!$A$2:$A$5,0)),"")

It's just putting a blank when there's an error (no entry in the relevant cell). Keep an eye on this thread though, someone more experienced may be able to offer a better alternative.

 

[pleeseemailme]

I think this is how it would look in your spreadsheet: You'd place this into the appropriate cell (I think Sheet 2 B column) and fill down. I assumed that A2 on Sheet two is where the value you want to match is.

=INDEX(Sheet1!$C$2:$C$6, SMALL(IF($A$2=Sheet1!$B$2:$B$6, ROW(Sheet1!$B$2:$B$6)-MIN(ROW(Sheet1!$B$2:$B$6))+1, ""), ROW(A1)))

Remember to hit ctrl+shift+enter not just enter when you're done adjusting the formula.

 

[yappa888]

Amazing! Thanks so much for all your helps!!!