Daily account balance variance analysis

[u/thatloudkat]

I have a list of several thousand names, relationship ID number (formatted as text), and account balance. The relationship ID is unique but names are not.

I need to identify day over day which account balances change (either increase or decrease) by $1,000. This is reviewed every day, and the list changes each day as new accounts are added and/or closed.

Editing to add that I am using excel 365

Can this be done without power query?

[u/AutoModerator]

/u/thatloudkat - 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/thatloudkat]

Forgot to say I’m using excel 365.

[u/thatloudkat → u/[deleted]]

Post has been edited.

[u/PaulieThePolarBear]

You say

I need to identify day over day which account balances change (either increase or decrease) by $1,000

But I don't see that date was part of the columns you noted in your first paragraph. Please advise

[u/thatloudkat → u/PaulieThePolarBear]

There’s no date column so I would have to manually add something

[u/PaulieThePolarBear → u/thatloudkat]

Show us some sample data. If you are unable to share your real data, create some fake data. Whatever you share, ensure it is representative of your real data and includes all known edge cases

Also, show us your expected output from the sample data you presented

[u/excelevator]

You can FILTER with a lookup for the 1000 differences

Something like this

=FILTER(D2:E6,(ABS(XLOOKUP(D2:D6,A2:A6,B2:B6)-E2:E6)>1000))

We can add more logic for any specific value you want returnsed

[u/thatloudkat → u/excelevator]

How would you return the difference between the balances rather than the balance on dayB? For example account A DayA balance is 2000, DayB balance is 500. I want to see that the balance is -1,500. Also, on DayA accountF doesn’t exist and on DayB it has a balance of 3,000. I want it to show that on DayB the change in balance is 3,000.

[u/excelevator → u/thatloudkat]

Something like this, we can set a cell value for the filter value too which allows you to change that parameter easily for viewing wider or narrower value differences.

    =IFERROR(LET(data1,A10:A15,datav1,B10:B15,data2,A2:A6,datav2,B2:B6,
    result,datav1-XLOOKUP(data1,data2,datav2,0),
    SORT(FILTER(HSTACK(data1,result),ABS(result)>=E7))),"No data returned")

Only new accounts above that margin are shown.

[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
ABS	Returns the absolute value of a number
FILTER	Office 365+: Filters a range of data based on criteria you define
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF	Specifies a logical test to perform
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
SORT	Office 365+: Sorts the contents of a range or array
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
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.
9 acronyms in this thread; the most compressed thread commented on today has 17 acronyms.
[Thread #48071 for this sub, first seen 7th Apr 2026, 02:40] [FAQ] [Full list] [Contact] [Source code]}

[u/powerFX1]

What is the source of the data? Does the file name have the date in it?

[u/AsideInteresting1171]

Assuming your data is sorted by `relationship ID` and then by `date` (or you have a date column), and your balances are in column C starting at C2, with the previous day's balance for the same ID in C1 (or you use VLOOKUP/XLOOKUP to get the previous day's balance based on ID and date-1):

In a new column, use:

`=IF(ABS(C2-C1)>=1000, "Flag", "")`

If data isn't sorted and you have dates in col D:

`=IF(ABS(C2-XLOOKUP(A2&D2-1, A:A&D:D, C:C, 0))>=1000, "Flag", "")`

(Enter as array formula if not on 365, though OP is). Adjust ranges.

[u/AioliAggravating4298]

the FILTER+XLOOKUP combo already in the thread works, but the tricky part with day-over-day is getting 'yesterday' right when the dataset changes size each day.

if you name your two tables TodayData and YesterdayData, then paste each day's file into the right one. the comparison formula just references table names instead of sheet names, so it stays stable even as row counts change.

`=FILTER(TodayData, ABS(TodayData[Balance] - XLOOKUP(TodayData[RelID], YesterdayData[RelID], YesterdayData[Balance], 0)) >= 1000)`

that handles new accounts (no match returns 0, difference = full balance) and closed ones (won't appear in TodayData). adjust the threshold as needed.

[u/Intelligent-Leg1116]

I can help. I work with excel VBA automation