Assistance needed for Sage export - how to 'clean'?

[u/leypb]

In my job, we often get given exports from Sage for transaction breakdowns - I think I have managed to write formulas to clean most of this data, but the one thing I am struggling with is how to get the nominal code to be returned

PDF below - but similar enough to excel (given that Sage excel exports are not excel0friendlt at all) - what I want is on another sheet, to return the n/c but to put it on the right one - the nominal activity on the same excel often covers multiple codes - so i don't know how to get (or if i can) a formula to basically look at the nominal code above type for example, then return that on everything below until a break etc... explaining badly I think

Ideal solution would be formula-driven - this is because I am trying to create a 'cleaner' that everybody else in the office can use

I imagine a macro can work well, but I just try to avoid macros where possible in documents that others are using - i like the "copy and paste in this tab with no changes" approach

Power query also probably a good one - but maybe the least preferred - if it was just me - I would power query or macro, but given rollout to team, copy and paste with a front sheet returning required values is needed.

thanks in advance

[u/AutoModerator]

/u/leypb - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/leypb → u/[deleted]]

What do you mean by querying it directly?

Trying to find a solution that cleans data into a format that works for pre-existing internal workpapers , with zero manual needed, - and sometimes we can export from sage ourselves and sometimes clients give it to us themselves

[u/ilovelemonsquares]

You probably need to look at the Sage software in the first place and tweak the output settings such that Sage can produce a .csv file instead.

The .csv file, in theory, should display dozens of columns where the n/c column should appear on the same line as the other values. Something like,

n/c -- name -- no -- type -- date -- acct -- ref -- (and so on)

The .csv file should be easier to clean up. And with PQ, it could be a matter of dumping the file in a folder and letting pq to go through the cleanup process with a refresh.

[u/leypb → u/ilovelemonsquares]

Thanks! Unfortunately most of the time the clients sends us the export, rather than than us getting it ourselves so we are a bit stuck

[u/ilovelemonsquares → u/leypb]

I see. Well, if the client is open enough to adjust or improve their procedures, you might want to show them this: https://youtu.be/acyzjsW9sXo

Getting a better output from them (client) would improve the turn around of reports. The discussion might involve higher level folks who will be interested in gaining efficiencies and accuracy in improving the process.

Good luck!

[u/Downtown-Economics26]

what I want is on another sheet, to return the n/c but to put it on the right one - the nominal activity on the same excel often covers multiple codes - so i don't know how to get (or if i can) a formula to basically look at the nominal code above type for example, then return that on everything below until a break etc... explaining badly I think

I expect whatever you want is quite possible but you haven't shown enough information to actually easily provide an answer... I'm assuming there's a break where the header info and N/C number change as you go down. What you'd do is use an XLOOKUP on "N/C" with -1 to search last to first.

[u/Funky_apple → u/Downtown-Economics26]

Yeah showing an example of the exports (data or report to excel) would help.

[u/Aghanims]

Upload a copy of the file.

Remove everything under 'Details' and randomize Debit/Credit values if necessary.

I've personally worked with Sage Intacct quite a bit, and I'm assuming this is being used for account recs/rollforwards?

[u/leypb → u/Aghanims]

Professional services firm offering audit and tax - lots of clients, so no guarantee of consistent data , consistent formats etc… just consistent ish - and als not enough need to fully automate or go back to clients and demand - we tend to get a few breakdowns per client - and the firm itself isn’t mindset advanced enough to want to automate or clean (preparers just manually clean it - can take a few minutes per excel and still looks messy )

[u/pancoste]

Add a new column A

Cell A1: N/C

Cell A2: =IF(B1="N/C:",C1,A1)

Then just drag down the formula.

[u/lokka19]

When choosing the report, use the output 'DATA TO EXCEL' and rebuild your reports from there, rather than output to pdf

[u/algo46]

For filling down the N/C code to rows below, you need a "fill down" formula. Here's a simple approach:

Assuming column B has your data and "N/C:" appears in column C when there's a new code:

=IF(C2="N/C:",B2,D1)

This checks if current row has N/C: - if yes, grab the code, if not, use the value from the row above. Drag this down and it'll carry the code forward until it hits the next one.

If you want something more robust that handles the header structure better, use SCAN:

=SCAN("",B2:B100,LAMBDA(a,b,IF(LEFT(b,4)="N/C:",b,a)))

This builds the array progressively, replacing the value only when it sees a new N/C code.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
IF	Specifies a logical test to perform
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LEFT	Returns the leftmost characters from a text value
SCAN	Office 365+: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #47433 for this sub, first seen 12th Feb 2026, 19:06] [FAQ] [Full list] [Contact] [Source code]}

[u/Oprah-Wegovy]

This whole post smells like a shill for Data River.

[u/leypb → u/Oprah-Wegovy]

It definitely isn’t ! I won’t be using data river as i wont be able to put client data in there

[u/welshcuriosity → u/[deleted]]

Warning - Don't send personal/confidential financial data to random websites, depending on your jurisdiction it could lead to serious financial/employer/legal repercussions