3 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Compare List of Numbers: Shift Cells up or Down without Changing Formula

TL;DR: I want to delete or add rows in B Column and shift the results up or down without the formula in C Column Shifting up or down.

So twice a day, I have to run a list of numbers and compare them to the previously generated list of numbers to see if any have been added. The first list I have to print and go line by line to see if they match up (because it is someone else printing the list and I can't copy and paste it). But to make it easier for me, at least the second time, I just populate the first and second lists I generate into columns A and B and then run formula (=A1=B1) in Column C(obviously copying all the way down so it changes each row). If everything comes back true, then it is done and I move on. If one pops up false, everything following will then be false. Now, to check the remaining numbers, I then have to delete the cell, shift the rest of the cells up. Only problem is it moves the B Column numbers in the formula up one as well. So it is no longer =A5=B5 it is =A5=B4. So I have to recopy and paste all the remaining formulas to get it to check the numbers properly again.

I thought I found a solution by making the cells absolute by doing =$A$1=$B$1 down the list, but it does the same thing. So I am just trying to lock Column C so that when I delete or add cells to B Column and shift the remaining numbers up or down, it keeps =A5=B5 in the formula and automatically updates to TRUE or FALSE as the numbers shift up and down.

Example:

A Numbers B Numbers C Formula
1 1 (=A2=B2) TRUE
4 3 (=A3=B3) FALSE
7 4 (=A4=B4) FALSE

When I delete B3 and shift cells up, this happens

A Numbers B Numbers C Formula
1 1 (=A2=B2) TRUE
4 4 (=A3=REF!) #REF!
7 7 (=A4=B3) FALSE

I would like it to just shift up the numbers in B Column and C Column remain unaffected except for updating the results like so

A Numbers B Numbers C Formula
1 1 (=A2=B2) TRUE
4 4 (=A3=B3) TRUE
7 7 (=A4=B4) TRUE

Any advice? as stated making the cells absolute =$A$1 does not seem to work. Trying to keep it to formulas, I am not comfortable with coding.

submitted by /u/IamTemplarKnightWork
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#AI formula generation techniques
#formula generator
#Excel alternatives for data analysis
#rows.com
#natural language processing for spreadsheets
#generative AI for data analysis
#Excel compatibility
#row zero
#real-time data collaboration
#financial modeling with spreadsheets
#real-time collaboration
#Excel alternatives
#formula
#numbers
#compare
#column
#shift
#row
#list
#delete