Compare List of Numbers: Shift Cells up or Down without Changing Formula
Our take
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.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Increment row but not column when copying a formula?Trying to fix an old spreadsheet at work. The current formula is this: =SUM(R6,R16,R26,R36,R46,R56) I need to build 4 more columns to the right of the existing one with the same formula, that increments the row but leaves the column the same, like this: =SUM(R7,R17,R27,R37,R47,R57) I don't think I can use $ for this as that always locks the column right? Photo Example below. I need to add three more Flav columns on the right hand side, pulling from lists like on the left hand side. https://preview.redd.it/v9iyxye9xrwg1.png?width=604&format=png&auto=webp&s=c0ad4b7502a0ec344221e29965de8dda99ed045e submitted by /u/fullofpaint [link] [comments]
- Anomalous Sorting of COUNTIF ColumnsEDIT: Resolved as I was trimming the worksheet to upload a version with data redacted with replacement text. As I was removing extraneous worksheets, the formula broke with a #REF value. When I fixed them, the problem resolved. Looks like I was actually connected to another set of the same data, but since it as outside of the table, it was creating the anomaly inside of it. Essentially the issue outlined in this blog article, except A) I am not using the unnecessary sheet reference that fixes the problem if it's removed and B) the formula displays correctly: https://excelcharts.com/excel-sort-countif-function-mess-fix-it-how-to/ (Unfortunately, the data I'm working on is proprietary, so I can't share specifics. :( Images are mockups that simulate the results I'm getting.) I have a large table on Sheet1. Column A is a Date column that runs back several years. I would like to count instances in text column B going one year back as of the most recent update and then sort that from largest to smallest, so I can make a simple line graph with the top ten items. Updated iterations of this graph will be used in a routine report, I'm ultimately trying to draft a plug and play tool so my Excel-deficient coworkers can just Copy/Paste into Powerpoint. https://preview.redd.it/ad56jbjp0oog1.png?width=364&format=png&auto=webp&s=8fc6b43dfe338b2c93d3ce7945d5fe9b7e45e0e5 On Sheet2 I create a secondary table that will have 2 columns. The text column A and the Count column B. I have entered each unique Data value from Sheet1 into the Data column in Sheet2. In the Count column I enter the following formula: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A#) (Where # equal the row) The table on Sheet1 has over 20 columns and I ultimately want to create line graphs for the data in several columns. Since there will be several of these graphs, and the data in Sheet1 is appended with new, up to date data at least once a week, it makes more sense to list the range as 'Sheet1'!A:A rather than 'Sheet1'!A1:A# The formula works just fine. https://preview.redd.it/0gpltacq2oog1.png?width=260&format=png&auto=webp&s=0ba8b74005b82fa9f37813dde8f838af29d49b96 But when I try to sort by Count, it does what's pretty much spelled out in the above linked article, moving the Data cell to the correct row, with the formula adjusting to reflect it's new replacement, but returning the value associated with the Data in the row it was in before the sort. So we see "Item27" in row 28 has the highest count, with 56 occurrences. The formula in row 28 column B, as expected, is: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A28) After the sort, "Item27" is in row 2 (as expected) but is returning a count of 4, the amount of occurrences "Item17" (now sitting in "Item27"'s old position in row 28) returned before the sort. Of course, "Item17" is returning the wrong count as well. "Item1" was obviously displaced by "Item27" and is in it's expected row 19 but returning a count of 56, which is "Item47"'s actual count. Essentially, each "Item#" is returning a count of whatever "Item#" replaced them in their old position in the table. https://preview.redd.it/aknjet2x5oog1.png?width=249&format=png&auto=webp&s=d069620eed3867df4eabae42532654c089f1cef7 HOWEVER, unlike the example in the article, the formula sitting in B2 next to "Item47" is still correct: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A2) So.... what's the issue and how do I resolve it? Version: Microsoft Excel for Microsoft 365 MSO (v2508) Environment: Desktop My Knowledge: Intermediate submitted by /u/LikeCCClockwork [link] [comments]
- Populate Cells From a Random Range of Cells, Based on the Contents of Different Cells.Hello Reddit pros, I am trying to create/find a formula that will do the following: Check if A2 matches values in D1:D6 and return a random value from E1:E6 into B2. The hard part is this; if A2 equals D1:D3 then the random value of B2 is from E1:E3, but if A2 equals D4:D6 then the random value of B2 is from E4:E6. For example: if A2 says Input 5, then B2 could be either Result D, Result E, or Result F. The values in the screenshot are just for demonstration; I will be able to extrapolate what I learn onto the spreadsheet I need it for. I am just curious if there is a formula to do that, or am I just having a lot of high hopes? I have tried using IF combined with INDEX and RANDBETWEEN but I cannot seem to get the formula correct for doing even the first part of what I need, let alone the second part. It looks like this: =IF(A2:A19,INDEX(D1:D3,RANDBETWEEN(1,COUNTA(E1:E3))),"") This obviously is not correct, and it returns a #VALUE error that I cannot figure out. I do not know the correct way to phrase the question to get a viable answer via internet searching, so I am once again turning to the experts on Reddit. Thanks for any insight. I am aware the formula on the screenshot is different than my post body; I deleted the top row but didn't fix the formula u/Connect_Camel_5998 solved it for me. Thanks everybody! The formula that was posted works great for what I needed! submitted by /u/ClandestineGhost [link] [comments]
- Don’t want formulae to auto update cell once data movesUsing formula =COUNTA($C$5:$C$6000) and have to add rows of data to the top a few times a day. Everytime I add data, $C$5 gets updated to the new location. I want it to always start at C5 The first 4 rows are not merged, but frozen. submitted by /u/remrem24 [link] [comments]