Value match (but not exact) like ctrl+F

[kesh123]

I've made a little example spreadsheet, it is attached.

I want to input reference number and pull the data off other sheets, if that number is found there.
But it only finds the values if the reference number is exactly the same.
With every reference number there actually come initials, from the person dispatching the box/pallet.
So in UPS sheets they would look like: 5628052MZ, 5630001KD...

I do not know who dispatched the box so Ive only got the numbers.
In that case values are not returned.

What shall I do to overcome this ?

>>EDIT: Ah yes I forgot about something. I have a carrier field. What I need:
If any value is found in any sheet, return the name of the sheet where value has been found to the carrier field.
So if I typed in the reference number and formula would find a date for it in "UPS" sheet, the carrier cell for that row would now say "UPS".
Is it possible?


>>EDIT2:
I've come up for this with the carrier field so far:

=IFERROR(IF(MATCH(main!$A3,ups!$A:A,0), "UPS", IF(MATCH(main!$A3,TFS!$A:A,0), "TFS", IF(MATCH(main!$A3,OT!$A:A,0), "OT", IF(MATCH(main!$A3,polarspeed!$A:A,0), "POLARSPEED")))),"")

But only returns value for UPS, doesnt go further. I know this is very crappy scripting of mine, so please help .


Many thanks,
Matt.

[Glenn Kennedy]

As you may have noticed.... nothing was attached.


Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[samba_ravi]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Phuocam]

Try:

=IF(COUNTIF(ups!$A:A,main!$A3), "UPS", IF(COUNTIF(TFS!$A:A,main!$A3), "TFS",
IF(COUNTIF(OT!$A:A,main!$A3), "OT", IF(COUNTIF(polarspeed!$A:A,main!$A3), "POLARSPEED",""))))

[kesh123]

Second try with attachment

>>EDIT: Thanks Phuocam this worked! Any ideas about finding matching parts of the value ?

cheers

[kesh123]

I've read about COUNTIF and it gave me an idea. Would it be possible to include COUNTIF into the formula to block it from finding values for the reference that is already there, in the main sheet?
What I mean is: You type in the reference, you get your values for it, later on you are mistaken and input the same reference number, but values show "Duplicate" or something now.
What do You guys think ?

[Glenn Kennedy]

Do you mean this:

I do not know who dispatched the box so Ive only got the numbers.

If you DO mean that, then why is Main A22 5628111MA.. If the MA is a mistake, then try this in M2, copied down:

=IF(COUNTIF(A$2:A2,A2)>1,"Duplicate",IF(SUMPRODUCT(--(LEFT(ups!$A$2:$A$8,7)+0=main!A2))>0,"UPS",IF(COUNTIF(TFS!$A:A,main!$A2), "TFS",

IF(COUNTIF(OT!$A:A,main!$A2), "OT", IF(COUNTIF(polarspeed!$A:A,main!$A2), "POLARSPEED","")))))

You will need to use a named range in the UPS sheet to auto-set the range. Before explaining that, let's get the other bits sorted first. Shading done manually for illustration....

[kesh123]

Yeah, I do not know initials, I put them there only for testing.
Thanks for that, I've got carried field almost solved.

So we would like to include your bit of code into this:

=IFERROR(INDEX(polarspeed!$E:E,MATCH(main!A2,polarspeed!$A:A,0)),"")

I've tried to but I've failed. help

I would like to specify that I do not know the size of UPS, TFS .. sheets.
Or is it what You are about to explain? With the named range...
Please.

[kesh123]

INDEX(polarspeed!$E:E,MATCH(main!A2,polarspeed!$A:A,0))

Include INDEX and MATCH with this bit:

=IF(COUNTIF(A$2:A2,A2)>1,"Duplicate",IF(SUMPRODUCT(--(LEFT(polarspeed!$A$2:$A$8,7)+0=main!A2))>0

So it finds data by part of the value, tells me if its a duplicate

[Glenn Kennedy]

You'll have to explain what you mean by "include my bit of code into this".

[kesh123]

Yeah this works for my carrier field as the value returned is value specified by me, because I know that it will say "UPS" if the data is found in UPS sheet.
What if to get my value I need my INDEX and MATCH, but I don't know how to put it together.
So it would do INDEX + MATCH but only if the reference is found (exact or part of it) and it tells me it is duplicate if I input it twice.

Since I could not put your piece of script together with my INDEX + MATCH, I've tried to come up with something on my own.
This gets my values, having 2 sheets "UPS" and "POLARSPEED".

=IF(ISBLANK(main!$A2),"",IF(LEN(main!$A2)=7,IF(COUNTIF(A$2:A2,A2)>1,"Duplicate",IFERROR(INDEX(UPS!$B:B,MATCH("*"&LEFT(main!$A2,7)&"*",UPS!$A:A,0)),IFERROR(INDEX(POLARSPEED!$B:B,MATCH("*"&LEFT(main!$A2,7)&"*",POLARSPEED!$A:A,0)),""))),""))

It finds the part of value and tells me about duplicated just fine BUT one problem.
No value returned if the match is exact.

Sometimes people forget to include their initials in the reference number and the match would be exact.
So how to make it find the match exact OR partial? Tried to mess around with OR funtion but failed as well.
Or how to put together INDEX and MATCH with SUMPRODUCT and LEFT and make it work ??

Sorry but I am begginner please be patient with me :p

[Glenn Kennedy]

The issue of data size is relevant ONLY for UPS. The formula for the others is happy to deal with whole column references. This is an array formula and will ignore balnks in UPS. I set it to look for 1000 rows. how many will you REALLY need? there are possible alternatives...

Still need an answer to my earler Q.

=IF(COUNTIF(A$2:A2,A2)>1,"Duplicate",IF(SUM(IF(ups!$A$2:$A$1000="",0,IF(--LEFT(ups!$A$2:$A$1000,7)=A2,1,"")))>0,"UPS",IF(COUNTIF(TFS!$A:A,main!$A2), "TFS",

IF(COUNTIF(OT!$A:A,main!$A2), "OT", IF(COUNTIF(polarspeed!$A:A,main!$A2), "POLARSPEED","")))))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Glenn Kennedy]

Sorry. I do not follow what you are trying to do.

1. How does the formula that I suggested NOT do what you want?

2. To explain where you want to go next, please attach a fresh sheet explaining your requirement.

[kesh123]

It does what I wanted. But only for carrier field where I do not need to use INDEX / MATCH.
I need to use INDEX/MATCH for other columns. I do not know how to do that.
How to use INDEX/MATCH so it finds exact match OR part match and tells me about duplicates.
This is what I need to do.

I got almost there on my own since I couldn't figure out how to base my code (with INDEX/MATCH) on yours, the one solving matching and duplicate issue.
That formula is in main sheet table column B. If You could help me own and do it your way with columns C and D , that would be brill.

Thanks

[kesh123]

Any thoughts?
Thanks

BUMP

Bump 2

[Glenn Kennedy]

Apols. Missed your reply.

In B2, copied across and down, an array formula:

=IF($E2="","",IF($E2="Duplicate","Duplicate",VLOOKUP($A2,LEFT(INDIRECT("'"&$E2&"'!A1:D500"),7)+0,COLUMNS($A:B),FALSE)))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Change A1:D500 to be compatible with the size of your data, but DO NOT use whole column references.

[kesh123]

Hey Glenn,

Thanks for help. This looks like this is going to work just fine.

And the formula is so neat and simple, much better than what I tried to achieve.
So no need for index/match to do what I wanted after all )

Thank You so much, this problem is solved.

I may need some help with conditional formatting but If I will, I am going to create a new topic for that.

Thanks again mate!

[kesh123]

Okay it is not going to work for me, because I do not understand it.

In my proper spreatsheets Ive got these values in different places.
So how should the formula know where to look for the data? (ups, polarspeed... sheets)
Also what does E2 represent in there? There is a carrier field in E2.
I've changed the formula with my new spreadsheet accordingly but doesnt work.

im noob

[Glenn Kennedy]

=IF($E2="","",IF($E2="Duplicate","Duplicate",VLOOKUP($A2,LEFT(INDIRECT("'"&$E2&"'!A1:D500"),7)+0,COLUMNS($A:B),FALSE)))

Red: The formula says, if E2 is blank return a blank, if E2 is "duplicate", return "duplicate" otherwise follow the formula.

Gold: Since the value in E2 is the name of a carrier, and also the name of the sheet where that carrier's data are kept, I used INDIRECT to pick up the sheet name from E2 and do a lookup to return the date, based on the invoice number (green). The formula looks for a match between the 7 digit invoice number in column A and the first 7 characters of the invoice number (cyan)

Since I have not seen your proper sheet, I have no idea where things are, or what you have done. I need to see your real sheet... or you need to really carefully check over a) if you asked the correct question, b) if your example was truly representative, or c) how you mis-transcribed the formula that I gave you.