Formula Help Please

[SARABECK]

Hi Everyone,
I have truck ids that comes through the ledger with their appropriate sales and purchase dollars. The dollars should offset when a sale and purchase IDs are the same. However, the issues i'm running into is spaces in between, zeros in between, letters in the beginning and letters in the end for these truck IDs.

I use the 2 formulas below to help but it is not capturing everything.

Formula 1 - To remove all the D in front.

=IF(LEFT(B2,2)="D*",RIGHT(B2,LEN(B2)-2),IF(RIGHT(B2,1)="D",LEFT(B2,LEN(B2)-1),B2))

Formula 2 - To remove extra spaces in between.

=SUBSTITUTE(C8," ","")

	Truck IDs	Formula 1	Formula 2	Problems
Sale	ACL123	ACL123		Letter in the Front
Purch	D*ACL123	ACL123		Letter in the Front
Sale	ABC 329	ABC 329	ABC329	Letter in the Front and SPACE - No Match
Purch	D*ABC329	ABC329	ABC329	Letter in the Front and SPACE - No Match
Sale	ABC 35060B	ABC 35060B	ABC35060B	Letter in the Front, SPACE and LETTER B - No Match
Purch	D*ABC35060	ABC35060	ABC35060	Letter in the Front, SPACE and LETTER B - No Match
Sale	PVHC20B	PVHC20B	PVHC20B	Letter in the Front and in the end - No Match
Purch	D*PVHC20	PVHC20	PVHC20	Letter in the Front and in the end - No Match
Sale	CC 01116	CC 01116	CC01116	Letter in the Front, SPACE and ZERO in between
Purch	D*CC1116	CC1116	CC1116	Letter in the Front, SPACE and ZERO in between
Sale	GOPV 036B	GOPV 036B	GOPV036B	Letter in the Front, SPACE, ZERO IN BETWEEN (need to remove B)
Purch	D*GOPV36B	GOPV36B	GOPV36B	Letter in the Front, SPACE, ZERO IN BETWEEN (need to remove B)
Sale	PVBL0099	PVBL0099	PVBL0099	Letter in the Front, Zeros in Between
Purch	D*PVBL99	PVBL99	PVBL99	Letter in the Front, Zeros in Between

<tbody>
</tbody>

Thank you.

Please delete my other "Formula Needed" topic. I submitted this again as my original topic was not loading probably. My apologizes.

 

[sheetspread]

Have you tried fuzzyvlookup?

http://www.mrexcel.com/forum/excel-questions/195635-fuzzy-matching-new-version-plus-explanation.html

 

[SARABECK]

Thank you for quick reply. This is not vlookup. I need a formula the will remove these things in the IDs.
Someone please help. I need the sale and purchase IDs to match.
Thank you

 

[sheetspread]

It matched the codes in your sample:

Excel 2010
	A	B	C	D	E	F
1	Type	Name			Type	Name
2	Purch	DACL123	ACL123		Sale	PVHC20B
3	Purch	DABC329	ABC 329		Sale	ABC 329
4	Purch	DABC35060	ABC 35060B		Sale	PVBL0099
5	Purch	DPVHC20	PVHC20B		Sale	CC 01116
6	Purch	DCC1116	CC 01116		Sale	GOPV 036B
7	Purch	DGOPV36B	GOPV 036B		Sale	ABC 35060B
8	Purch	DPVBL99	PVBL0099		Sale	ACL123
Sheet2

Cell Formulas
Range	Formula
C2	=fuzzyvlookup(B2,$F$2:$F$8,1)

That should work for what you're trying to do

 

[SARABECK]

Thank you sheetspread. Can you please help and show how i can set this up?

Will this work if the sale and purchases are only known by their IDs. I mean, For easy illustration, i added the sale and purchase column, otherwise, it shows up in the report as ABC 329 in row 3 colum A (for example) and D*ABC329 on row 150 column A.

I thought if i have formula i can drag it down and then do a conditional formatting for duplicated and it should offset.

Thank you so much.

 

[SARABECK]

My formula is incomplete:

Original IDs:

GOPV 036D D*GOPV36D

My formulas: 1:=IF(LEFT(B65,2)="D*",RIGHT(B65,LEN(B65)-2),IF(RIGHT(B65,1)="D",LEFT(B65,LEN(B65)-1),B65)) 2:=SUBSTITUTE(C65," ","") 3:=LEFT(D66,LEN(D66)-(RIGHT(D66)="D"))

<tbody>
</tbody>

Results:
GOPV036
GOPV36

I need help to get rid of the zero in between. so that it is an exact match.

Thank you.

 

[sheetspread]

Copy Alan's code from post #2 of the link, paste it into a module, and the formula will work. I think you do need the Purchases/Sales column and set up the table like I did above with a column C header like "Common ID" to use as your standard. Then each line will have the same code and you can apply CF from there.

 

[SARABECK]

Thank you for your help.

 

[Peter_SSs]

You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

If it fails for any data, please provide samples of that data and the expected results.

Function TruckID(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "^(D\*)?(\D+)(0*)(\d+)(\D)?$"
    TruckID = .Replace(Replace(s, " ", ""), "$2$4")
  End With
End Function

Excel Workbook
	B	C
1	Truck IDs	Extracted ID
2	ACL123	ACL123
3	D*ACL123	ACL123
4	ABC 329	ABC329
5	D*ABC329	ABC329
6	ABC 35060B	ABC35060
7	D*ABC35060	ABC35060
8	PVHC20B	PVHC20
9	D*PVHC20	PVHC20
10	CC 01116	CC1116
11	D*CC1116	CC1116
12	GOPV 036B	GOPV36
13	D*GOPV36B	GOPV36
14	PVBL0099	PVBL99
15	D*PVBL99	PVBL99
Truck IDs

 

[SARABECK]

Peter_SSs, Thank you so much! Just awesome! thank you!!.

Peter_SSs, I would like to apply this to my Rail IDs, the concept is the same except the Rail IDs starts with B in stead of D. Please don't add to this coding i would like to keep them separate.

I try to change the coding below but not working for me.

Function TruckID(s As String) As String
  With CreateObject("VBScript.RegExp")
 [B]   .Pattern = "^(B\*)?(\B+)(0*)(\b+)(\B)?$" [/B]
 [B]  TruckID = .Replace(Replace(s, " ", ""), "$2$4") [/B]
  End With
End Function

Thank you again.