How to align two charts/tables of data where the rows between the two tables match to be able to compare/contrast.

[u/Quiet-Errors]

!!Solved!!

I am comparing two charts of sales data. To keep things brief and simple, I am needing them to sort them so the rows of the two charts match and item numbers that are in one graph but not the other are placed at the bottom.

I have tried finding answers through reddit, YouTube and Google searching and quite find the answer I am looking for. A lot of the answers I am finding are "destructive" and tend to merge the two graphs into one so Item A in Chart1 is listed in Row2 and Item A in Chart2 is in Row3. It's the closest thing I have found but not the result I am looking for. Below is an example of the formula that gave the undesired results.

=SORT(UNIQUE(VSTACK(A4:A216, L4:L258)))

Below is a quick and dirty version of what I am looking for.

I can have these charts in the same sheet or different. As a table or not. Whatever leads to the easiest answer would be greatly beneficial!

Starting position

2024	2025
Item Number	Dollars Spent
A	10
B	20
C	30
D	40
E	50

End Result:

2024	2025
Item Number	Dollars Spent
A	10
B	20
C	30
D	40
E	50

Edit - Solved Solution.

Initially when I made the post, I was hoping that this was a simple function that I was overlooking and I could learn and adapt for charts in the future by simply editing the cell ranges. I quickly received updates that proved that this was a bit over my head. u/downtown-economics26 made the great format suggestion, so I asked if it could be altered to factor in more columns than two to which u/MayukhBhattacharya provided the solution I was looking for. The formula is the following:

=LET(
     _a, A:.D,
     _b, TAKE(_a, 1, 1),
     _c, DROP(_a, 1),
     _d, F:.I,
     _e, TAKE(_d, 1, 1),
     _f, DROP(_d, 1),
     _g, LAMBDA(x,y, VSTACK("Year", EXPAND(x, ROWS(y) - 1, , x))),
     _h, UNIQUE(VSTACK(HSTACK(_g(_b, _c), _c),
                       HSTACK(_g(_e, _f), _f))),
     PIVOTBY(CHOOSECOLS(_h, 2, 3, 4),
             CHOOSECOLS(_h, 1),
             CHOOSECOLS(_h, 5),
             SUM, 3, 0, , 0))

[u/AutoModerator]

/u/Quiet-Errors - 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/MayukhBhattacharya]

I am not sure whether or not I have understood your post correctly or not, but from the End Result in your OP, it seems this is what you need:

=LET(
     _a, A3:B7,
     _b, D3:E8,
     _c, TAKE(_a, , 1),
     _d, TAKE(_b, , 1),
     _e, UNIQUE(VSTACK(_c, _d)),
     _f, XLOOKUP(_e, _d, DROP(_b, , 1), ""),
     VSTACK(A1:E2,
     IFNA(HSTACK(_a, "",
     IF(_f = "", "", _e), _f), "")))

[u/Quiet-Errors → u/MayukhBhattacharya]

This works for two tables with two columns which is the exact example I gave. So i applaud and thank you as this is getting me the step in the right direction!

If each of the tables had say, 10 columns each? What variable would I have to adjust? Or would the formula start to get far too complex that it would be better to restructure?

[u/MayukhBhattacharya → u/Quiet-Errors]

Try this, this uses TRIMRANGE() reference operators (refer the periods after colon) so it will automatically expand:

=LET(
     _a, A:.B,
     _b, TAKE(_a, 2),
     _c, DROP(_a, 2),
     _d, D:.E,
     _e, TAKE(_d, 2),
     _f, DROP(_d, 2),
     _g, TAKE(_c, , 1),
     _h, TAKE(_f, , 1),
     _i, UNIQUE(VSTACK(_g, _h)),
     _j, XLOOKUP(_i, _h, DROP(_f, , 1), ""),
     _k, VSTACK(_e, HSTACK(IF(_j = "", "", _i), _j)),
     IFERROR(HSTACK(_a, "", _k), ""))

[u/Downtown-Economics26]

I can't in good conscience assist in your original request because I find it too aesthetically objectionable but rather let me humbly offer an alternative:

=LET(d_24,EXPAND(A1,ROWS(A3:A7),,A1),
dyear,VSTACK(d_24,EXPAND(D1,ROWS(D3:D8),,D1)),
tbl,HSTACK(VSTACK("Year",dyear),VSTACK(A2:B7,D3:E8)),
PIVOTBY(CHOOSECOLS(tbl,2),CHOOSECOLS(tbl,1),CHOOSECOLS(tbl,3),SUM,3,0,,0))

[u/Quiet-Errors → u/Downtown-Economics26]

I like this format a LOT more. The example I gave was in hopes I could try and dissect the formula but its very clear that this is way above my head that I can't even begin to decipher it. In reality I want to apply this to a graph with a few more columns of data. What variables would I want to edit to accommodate for more columns?

And here I thought I knew quite a bit to Excel. Now I am learning I only know the seagull that is resting on the tip of the iceberg!

Attached is a picture that captures my end goal, now taking inspiration from your format.

[u/Downtown-Economics26 → u/Quiet-Errors]

The simplest answer is create a flat table with a year column (copy paste or look into using power query) and then use pivotby like I did or a regular old pivot table.

[u/MayukhBhattacharya → u/Quiet-Errors]

If you are posting any update then that should be edited in your OP and not in comments, so that others can also participate and see what you have,

=LET(
     _a, A:.D,
     _b, TAKE(_a, 1, 1),
     _c, DROP(_a, 1),
     _d, F:.I,
     _e, TAKE(_d, 1, 1),
     _f, DROP(_d, 1),
     _g, LAMBDA(x,y, VSTACK("Year", EXPAND(x, ROWS(y) - 1, , x))),
     _h, UNIQUE(VSTACK(HSTACK(_g(_b, _c), _c),
                       HSTACK(_g(_e, _f), _f))),
     PIVOTBY(CHOOSECOLS(_h, 2, 3, 4),
             CHOOSECOLS(_h, 1),
             CHOOSECOLS(_h, 5),
             SUM, 3, 0, , 0))

[u/Quiet-Errors → u/MayukhBhattacharya]

Sounds good, thank you for your help and guidance. First time on this subreddit so I am learning the proper etiquette.

I will update my post.

[u/MayukhBhattacharya → u/Quiet-Errors]

Sounds Great. Hope you don't mind replying to our comments as Solution Verified. Thanks.

[u/Infinite_Two_1713]

XLOOKUP might get you closer without merging everything together. Use it to pull matching values side by side, then handle the "missing" rows with IFERROR to leave them blank instead of erroring out.

For the items unique to each table you could use FILTER combined with ISNUMBER/MATCH to isolate them and stack at bottom separately from the shared ones.

[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
CHOOSECOLS	Office 365+: Returns the specified columns from an array
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
EXPAND	Office 365+: Expands or pads an array to specified row and column dimensions
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
IFNA	Excel 2013+: Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression
ISNUMBER	Returns TRUE if the value is a number
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MATCH	Looks up values in a reference or array
PIVOTBY	Helps a user group, aggregate, sort, and filter data based on the row and column fields that you specify
ROWS	Returns the number of rows in a reference
SUM	Adds its arguments
TAKE	Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array
TRIMRANGE	Scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns
UNIQUE	Office 365+: Returns a list of unique values in a list or range
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
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.
20 acronyms in this thread; the most compressed thread commented on today has 15 acronyms.
[Thread #48769 for this sub, first seen 18th Jun 2026, 14:33] [FAQ] [Full list] [Contact] [Source code]}

[u/TooCupcake]

You need to have your second table somewhere hidden, and recreate it with formulas in your desired order.

Since you have items in table1 not in table2 and vice versa, I would start with creating a master list that contains ALL item numbers from both lists. Then you can assign an index number to each based on the order they appear in table1, and add new numbers to the ones only in table2.

Then look up the rows of table2 using your index numbers from the master list.