Forum Home
Press F1
Thread ID: 28220	2002-12-13 19:08:00	Formulas in Excel	Mike (15)	Press F1

Post ID	Timestamp	Content	User
105773	2002-12-13 19:08:00	I've got a fairly large Excel spreadsheet with around 25000 rows of data in 2 columns. I've been comparing the two rows using an "IF" calculation (basically if A1=B1 then display a "1" in C1, else display a "0"). Now wherever there's a "0" I need to insert one or more cells in column A to push the data down until the cell in column A = the cell in column B. However when I do this, the formula in column C follows the cells down, so it'll now say IF(A4=B1,1,0) instead of still looking at A1. Is there any way to insert new cells, but not have it effect where the formula is looking? I'm not inserting cells into column B at the same time, so only column A is getting the blank cells. When I add cells into B, column A doesn't get a blank one. Does that make sense? I wasn't too sure how to explain what I was doing. Mike.	Mike (15)
105774	2002-12-13 20:09:00	Try switching to relative referencing (Tools / Options / General - tick "R1C1 reference style". This will change your formulas to something like: =IF(RC[-2]=RC[-1],1,0) Remove the explicit row references in the formula: =IF(C[-2]=C[-1],1,0) and you should be able to insert cells without upsetting any row reference. :)	wuppo (41)
105775	2002-12-13 20:26:00	Prepackaged formulae can be frustrating whereever they are from, since they are only designed to do limited straightforward jobs. Try the following macro (if you have VB) which is just a slightly more complex function. It can be easily altered. Sub Macromike() 'count entries in B column n = 0 Do While IsEmpty(Cells(n + 1, 2)) = False n = n + 1 Loop Cells(1, 5) = "count of entries in B column = " & n 'shift A column down if cells in A and B not equal For i = 1 To n Cells(i, 3) = 1 If Cells(i, 1) <> Cells(i, 2) Then Cells(i, 3) = 0 Cells(i, 1).Insert shift:=xlDown End If Next i End Sub	rugila (214)
1