String Manipulation through customer text to column fixed length option or Instr option

[Rom72]

Hi all,
I need help with a code for a automation tool I am developing.
I have attached a sample file. I will explain the context here.
To do a match between data set ,I want to ensure the common range between both the dataset is same ( which is the Stmt Inv & Sysm Inv)

I want a solution where the user can select the length of string in the selected range by inputting the length of the text ( like excel function =right(“text”,”variable value assigned by the user”)

Or else

is it possible to do comparison between Stmt Inv range & Sysm Inv range, then change the Stmt Inv range to exactly match Sysm Inv. This is possible because
Sysm Inv is a subset of Stmt Inv

I apologies if I haven’t explain the problem clearly,

Cheers
Rom

 

[Luke M]

Hi Rom,

I'm afraid you lost me. I didn't see any code in the attached workbook. Also, there were 3 worksheets...which sheet/cells should I be looking at? Which cells are we comparing?

 

[Deepak]

Hi,

IN addition to what Luke M said.

You may try this..

Sub test()
Dim r, r1 As Range

Set r1 = Sheets("End Result").[C8:C14]

For Each r In r1
    If r.Value <> r.Offset(0, 1).Value Then
        r.Value = r.Offset(0, 1).Value
    End If
Next

End Sub

 

[Rom72]

Hi Luke sorry about the late reply,
Please look at the tab "End Result", I need the data under the column heading "Stmt Inv" to be convereted into the cleaned data which is under the column "Cleaned Stmt Inv".

I see two option to achieve this, either i want the user to decide, what should be length of the string, if the user select the string length(the length can vary hence letting user decide the length). for eg right("text", "variable input from user")

Other option -
is it possible to extract the Sysm Inv from Stmt Inv to the "Cleaned Stmt Inv" column as System Inv number is a subset of Stmt Inv.

for Eg If Stmt Inv range (“BB”) has a value "AB106481", then there will be a value in the "System Inv" range (“D8:D14”) which will be "481" , so i want vba to look through the entire "System Inv" range, identify "481" & paste that output to (“C8”)

I have uploaded new file which i hope is less confusing

 

[Rom72]

Hi,

IN addition to what Luke M said.

You may try this..

Sub test()
Dim r, r1 As Range
 
Set r1 = Sheets("End Result").[C8:C14]
 
For Each r In r1
    If r.Value <> r.Offset(0, 1).Value Then
        r.Value = r.Offset(0, 1).Value
    End If
Next
 
End Sub

Hey Deepak,
I am afraid, this is not the solution i was looking.Sorry about my complex explaination of my problem.
Thank you for taking out time & effort.

 

[Rom72]

The excel file

 

[Luke M]

...for Eg If Stmt Inv range (“BB”) has a value "AB106481", then there will be a value in the "System Inv" range (“D8:D14”) which will be "481" , so i want vba to look through the entire "System Inv" range, identify "481" & paste that output to (“C8”)

I'm not sure still how System Inv corresponds with the previous two columns, as the numbers do not match up. Nevertheless, here's a macro which uses User input.

Sub StringExtract()
Dim xChoice As Variant
Dim startRange As Range
Dim c As Range

'Many ways you could define these, using hard code for now
Set startRange = Range("B8:B14")


xChoice = InputBox("How many characters to extract?", "Character Exctraction", 1)

'User cancelled of invalid
If xChoice <= 0 Or xChoice = "" Then Exit Sub

Application.ScreenUpdating = False
For Each c In startRange
    c.Offset(0, 1).Value = Right(c.Value, xChoice)
Next c
Application.ScreenUpdating = True
End Sub

 

[shrivallabha]

Could you confirm if following works for you?

In cell C8:
=RIGHT(B8,LEN(D8))
and then copy down

 

[Jon von der Heyden]

It strikes me that you want to reconcile 'Statement' to 'System Data' (and/or vice versus) - correct? And the challenge you are confronted with is that the statement references the complete invoice number but the system data only references an element of the invoice number (or variable character length, which is where your question arises, I suspect).

If my assumption is correct, try:

1. Go to the 'System data' sheet
2. In D4 enter this formula: =VLOOKUP("*"&B4,Statement!B:C,2,FALSE)
3. Copy D4 and paste down to D10

I hope this helps.

Jon

 

[Rom72]

Hi Luke,Shrivallabha & Jon,

Thank you all for your help.All the solutions work.More over it was a great learning experience.
I am still testing it to see whether it can handle all possible scenarios ( input data quality is very poor) but i believe i got the direction to move forward.

Thank you once again

Cheers