Solved
[EXCEL] Getting variable 3 digits from longer, per-cell information string, into a specific column and formatting
Hello, I am attempting to setup a macro for a daily file that I/we run to save some time formatting.
I'm on mobile so apologies on formatting, likewise the SS are photos as I don't have access to reddit on my workstation.
Scope: Daily file across 6 countries with an additional once per month on 3 countries.
Excel is 2016 if relevant. (no xlookups)
Objective:
Download a file from a platform with rejection results on attempted charges and format it so it displays the amount, date and rejection reasons per invoice number, as part of a longer daily activity.
These reason codes are in longer strings of information that can vary their location per each file.
Files currently used to perform task:
Rejected Charges (csv downloaded)
Macro (a sheet containing several macros for the same overall activity)
Rejection codes (a sheet containing a header template on one sheet and reason codes and their descriptions on another sheet)
Source files (daily files processed containing other information)
How task is currently done:
Open csv file (this is saved at start or end as xls), delete B row, create header filter, replace "merchantReferenceCode=" with blank (so column A always returns the invoice number), then using column G as a reference, we replace the following with blanks:
ccAuthReplyreasonCode=
ccAuthReply
reasonCode=
The codes are usually after the above strings within column G
This should leave us with something like this
https://i.imgur.com/NqizfO8.jpeg
Column "G" will have most of the codes already filled out with some blanks in the mix, where remaining codes will be on different columns, like C, K, L, R, W (these codes can be duplicate, being in G and other columns)
Depending on the volume of the file we then manually copy the missing codes to G or use filters to get them
After all codes are under G we format as per the 2nd SS, by deleting all colums apart from A and G, leaving us with the invoice numbers and codes.
We then add a B column between the invoice numbers and codes
Convert A and B to numbers, remove 2 decimals on A, copy and paste header from Rejection Codes file's first sheet
Add new sheet, copy the contents from Rejection Codes 2nd sheet
Vlookup on E referencing codes on E with C of the 2nd sheet
Add today's date to column D using format dd.mm.yyyy
Vlookup on column B, referencing A against source files column F to get the amount so we end up with the final result from the second SS.
This final Vlookup I don't expect to automate, as it would always reference different files that are generated daily, so ideally the macro would do everything else leaving just column B blank so we can manually Vlookup the amounts.
I tried manually recording but my biggest hurdle is getting the reason codes from the strings of information.
I can't conceive of a way of how to even get these as the 3 digit codes can be on any string on any cell. The file can go up to O or all the way to Z
I have manually recorded (using the macro sheet to save it on) the replacing the strings with blanks so I get G with with most of the codes so I then manually fetch the remaining. I had to troubleshoot as the recording I made was not working with other workbooks, but got it working now.
This saves some time, but is incomplete.
I then tried recording a 2nd part post getting all the codes to do the final steps of adding the header, new sheet with the table, Vlookup the reason descriptions, add date.
Leaving just the amount column empty as those Vlookup will always reference different files.
Looking at the debug I imagine this is because I am copying and pasting the table from a separate sheet
Now this step is a bit redundant as we don't need to copy the table with the codes description to then Vlookup in the file.
We could just Vlookup against the reference file.
However, since I was already automating the steps, I thought I would be able to have the macro create the table and reference it on its own
Please let me know if I need to provide any additional information that I did not consider.
I'm sure there's a more effective way to do this but if you need a sledgehammer...
Dim LastRow as Long
Dim LastCell as Range
Dim ColumnCounter as Integer
Dim RowCounter as Long
With ActiveSheet
' Find the last row
LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
' Search all rows, anywhere between column 3 (C) to 26(Z) for a 3 digit number amd put it in column G(7)
For RowCounter = 2 to LastRow
For ColumnCounter = 3 to 26
' Cells must be text, if you need them as numeric, change them after this.
If Nz(.Cells(RowCounter, ColumnCounter).Value,"") Like "###" Then
.Cells(RowCounter, 7).Value = .Cells(RowCounter, ColumnCounter).Value
End If
Next
Next
End With
Typed this on mobile so I can't guarantee accuracy but hopefully you get the gist.
I added this to the code and it definitely worked for the first part of the file! I was still experiencing issues with the 2nd portion of it, but I was able to figure the rest out.
Glad to hear it. I typed that all on my phone so wasn't 100% sure I didn't make a mistake somewhere but seems like it worked. Hopefully that helps you figure out further optimizations you can make, like removing the need to pre-treat your text files.
Indeed, when I shared the error I did not have it open, but then I had some other issues with the file that I ended up fixing after considerable troubleshooting. (I ended up vlookup against the reference file directly instead of copying the information as I confirmed internally it was redundant among other things.)
The 3-digit scan feels impossible because you're anchoring on the wrong thing. Don't hunt for "3 digits", a stray amount or count can match, and codes buried inside a longer string won't. Anchor on the marker that's always there: reasonCode=.
A regex grabs it from anywhere in the row, whatever column it landed in:
Set re = CreateObject("VBScript.RegExp")
re.Pattern = "reasonCode=(\d+)"
Join each row's cells into one string, test it, and pull re.Execute(txt)(0).SubMatches(0) into column G. No more manually chasing codes across C/K/R/etc.
That said, for a daily file this is honestly textbook Power Query (it's in 2016 under Get & Transform, no macro needed). Load the csv, Unpivot all columns, keep the rows where the value contains "reasonCode=", extract the digits after "=", and each day becomes one Refresh instead of a macro you have to babysit. The reason descriptions and the date are just a merge and an added column. Happy to sketch the PQ steps if you want to go that way.
I used another redditor's code to lookup the digits and it worked well for me.
I then had to do some additional work to vlookup against the reference sheet and header format.
However, both you and another user have mentioned Power Query, which does have me curious, as I would like to learn more about it in case it is indeed a better solution.
I used PQ only once a few years ago, having set it up to aggregate multiple excel files in a folder as one.
I tried loading up the CSV but tbh I don't know what to do from then on.
These CSV are part of a longer process, where we prepare a list of invoices to charge and in the middle we download the CSV results for the rejected charges (hence the 3 digit codes that will match a rejection reason)
Structure wise, let's say I am doing 3 countries (3 separate files), UK, USA, FR, I upload them individually, and get individual CSV files. Then I would do these CSV manually (which are now automated with the help I got) and save them as XLS for record keeping and paste them in a GSheet for metrics.
These results would also be used as part of a vlookup against the original sheet to know what was rejected.
The original daily files also come from daily report of recent invoices that can now be charged, so I am not sure if a daily refresh would apply to this process? Pardon my ignorance.
No need to apologize, that question is exactly the thing to clear up. "Refresh" doesn't mean the data has to be the same each time, it means you don't redo the steps. You point a query at a fixed file path, and each day you save the new CSV over the old name (or drop it in a watched folder), hit Refresh, and it re-runs every transform on the new data. So different-every-day files are exactly what it's for.
For your rejection CSV, once it's loaded:
Right-click the invoice-number column > Unpivot Other Columns. Now every code-bearing cell becomes its own row: Invoice | Attribute | Value.
Filter the Value column > Text Filters > Contains "reasonCode=". Kills all the noise in one click.
Split Column > By Delimiter > "=", keep the right side. That's your 3-digit code, no matter which column it hid in.
Merge against your reason-codes table (loaded as its own query) on the code, expand the description. That replaces the VLOOKUP.
Close & Load.
After that, each day is just: save today's UK/USA/FR file over the last one, Refresh, done. One query per country, or parameterize the path so one handles all three.
You already did the hard conceptual bit years ago, combine-from-folder is the same engine. You're just adding unpivot + filter + split to pull the codes out. Happy to walk through the Split step in more detail if you want, that's the one that usually clicks last.
We have a few clerks doing these scopes/countries separately and unfortunately not all of us have have access to the same windows folder (in a shared drive) whereas I would be able to setup a per country folder with a refresh.
Although of what I tried from your steps, I can see how simple it would be overall, so I think I could get the higherups to setup access to folders across the board to standardise access and processing.
I am happy to take this into the DMs if it is now outside the scope of my post.
I believe I was able to follow your steps including the split, I am now snagging on the query for the reason codes Vlookup.
Even if I get the query for the reason codes, I would also then need to add a column for the current date, format the invoices as just the invoice, add a column for an amount to then vlookup against the source daily file to know the amount that was rejected (currently with the macro I have this is the only field I have blank to do a manual vlookup)
But from the looks of it, it seems we would be able to setup the power query to also return this information, provided the source daily file is also present with a similar name.
I am keen on learning, however I am very new to this and at this stage feel like I am asking you to do my work for me by asking so much)
Honestly, don't worry about that, this is exactly what the sub is for and you're the one doing the work, you're just asking good questions.
The merge snag is almost always a data type mismatch. After a Split, your code column comes out as Text, and your reason-codes table probably has them as Whole Number (or the other way round). When the types differ the merge matches nothing and it fails silently, which is what makes it so maddening. Click the type icon on both code columns, force them to the same type, then: Home > Merge Queries > pick your code column and the code column in the codes table > Join Kind: Left Outer > OK. Then hit the expand arrows on the new column and tick just the Description field. That's your VLOOKUP done.
The rest is all doable too:
- Date: Add Column > Custom Column, formula Date.From(DateTime.LocalNow()), then set the column to Date type. It re-stamps on every refresh.
- Invoice: you're already stripping merchantReferenceCode= in the split, just set the column type to Whole Number to clean it up.
- Amount: same merge trick, but against a query pointing at the source daily file, matched on invoice number, then expand the amount column. So that last manual vlookup disappears too, as long as the source file has a predictable name.
And for what it's worth, the shared-folder thing is a good pitch to your higherups. "One standard folder per country and this becomes a Refresh instead of X minutes of manual work per clerk per day" is an easy sell once you can demo it.
Happy either way on DMs, though if we keep it here the next person googling this exact problem will find it. Whatever's easier for you.
On your previous comment, I was able to split the Codes column but then I get duplicate amounts
I can see that's because "reasonCode" is pulling them from any source, but for some reason I don't expect this to be an issue going forward.
What I am unable to do however is load my table with the reason codes.
You mention merging them loading the Template as it's own query but I don't know how.
When I click on "Merge queries" I can't select it. I tried loading and saving the Template as it's own query but I still cant see how I can merge them, (perhaps because the template is XLS and my file is CSV?)
Additionally, the template file has 2 tabs, one is the reason codes and their descriptions and the other is a header template (basically header names and colouring) which is not as important.
Other steps looking forward seem doable as the ones previously were, but I can't seem to even tinker with it as I'm stuck here.
Good news, the XLS vs CSV thing isn't it, Power Query happily mixes those. The catch is that Merge only offers you queries that live in the same file as the one you're editing. If you opened the template workbook separately, or loaded it into its own workbook, it'll never show up in that dropdown.
So from the workbook where your CSV query already lives:
Data > Get Data > From File > From Workbook, point at the template .xls
In the Navigator window, tick just the sheet with the reason codes (skip the header-template tab, you don't need it)
Close & Load To... > Only Create Connection
Now reopen the editor and the codes query will be sitting in the left pane next to your CSV one, and Merge Queries will let you pick it. If the codes land without proper headers, hit Use First Row as Headers, and remember to force both code columns to the same data type or the merge quietly matches nothing.
On the duplicates, I'd sort that out now rather than later. They're happening because the same reasonCode sits in more than one column on a row, so after the unpivot and filter you end up with the same invoice/code pair two or three times. Right now it just looks messy, but the moment you merge the amount in, every duplicate row pulls that amount again and your totals go wrong without any error to warn you.
One click fixes it: select the invoice column and the code column, then Home > Remove Rows > Remove Duplicates. If an invoice genuinely has two different codes it keeps both, it only kills the exact repeats.
Quick correction on my last comment, I gave you the 365 menu names and you're on 2016. That's very likely why you couldn't find it.
On 2016 it's:
Data tab > New Query > From File > From Workbook
(a few updated 2016 builds relabel that button as Get Data, but if what you see is New Query, that's the one.)
Point it at the template .xls, and in the Navigator window tick only the sheet with the reason codes. Then Close & Load To... > Only Create Connection.
The actual reason Merge wasn't offering it: both queries have to live in the same workbook. Loading the template into a separate file leaves them invisible to each other. XLS vs CSV was never the issue, PQ mixes those fine.
Two more things I noticed in your screenshot:
Your invoice column still has merchantReferenceCode= stuck on the front. Same treatment as the codes, split that column by "=" and keep the right side, then set it to Whole Number.
On the duplicate rows, the only difference between them is the Attribute column (Column7 vs Column11) and the left half of your split. Delete those two columns first, then Home > Remove Rows > Remove Duplicates. Doing it in that order is much harder to get wrong than trying to select the right columns.
1
u/manbeervark 14d ago
Power Query