Finding the Next Match (text)

yappa888

New Member
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:

A
B
C
1
Part Produced
Serial Number
2
Handle
1001
3
Seat
2000
4
Handle
1002
5
Handle
1003

<tbody>
</tbody>


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)...
A
B
1
Part Ordered By Customer
Serial Number
2
Handle
1001
3
Handle
1001
4
Seat
2000
5
Handle
1001

<tbody>
</tbody>

The formula will need to go into Sheet 2 'B' Column, and I'd like it to come out looking like this...
A
B
1
Part Ordered By Customer
Serial Number
2
Handle
1001
3
Handle
1002
4
Seat
2000
5
Handle
1003

<tbody>
</tbody>


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

Board Regular
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
Handle1Handle1001
Seat1Seat2000
Handle2Handle1002
Handle3Handle1003

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


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

New Member
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

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

yappa888

New Member
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.
 

rossa

Board Regular
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

Board Regular
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.
 

Some videos you may like

This Week's Hot Topics

Top