#1
|
|||
|
|||
Problems with lookup/search Formula
Hello, I’m trying to figure a formulas for columns A and B so the example on the left will look like the one on the right where the empty spaces in columb J and K are filled in. The number of rows for example in project 234 may be as few as one and as many as 30. If column C has no data then column A, B and D will also not have data. There will never be empty rows until the last row of data. Any ideals? Thanks
|
#2
|
|||
|
|||
Would VBA or some sort of macro be better?
|
#3
|
||||
|
||||
There are different techniques described at 3 Ways to Fill Down Blank Cells in Excel - Excel Campus
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
One possible way.
After getting a new (calculated) table, you can replace all formulas in it with values (Copy - PasteSpecial>Values), and after that you can delete the original table. |
#5
|
|||
|
|||
Will you be typing in the first value in each instance (for example in cells A2 and B2, A6 and B6)?
I'm not quite sure of your full intentions, but I would think that something simple like =if(C3="","",A2) placed in cell A3 would copy the A value above if the sector value is not empty. You'd do the same for B, and it would copy the value directly above, all the way down. Then you could manually fill in where the values change. You could also simply merge the values with as many blank cells exist underneath. A bit of VBA code to go that would look like: Sub Merge_Cells() Dim blnks As Range, r As Range Dim c As Long For c = 1 To 2 On Error Resume Next Set blnks = Columns(c).SpecialCells(xlBlanks) On Error GoTo 0 If Not blnks Is Nothing Then For Each r In blnks.Areas With Union(r.Cells(0), r) .Merge .VerticalAlignment = xlCenter End With Next r End If Next c End Sub Of course you may not want it vertically centered. Also, this doesn't work with some imported data where the blank appearing cell isn't quite blank, so if you have issues with it, examine the cell contents and make sure they are empty. |
#6
|
|||
|
|||
Thanks to all who helped out here. At the moment I am using the ArviLaanemets solution and it seems to be working fine.
|
#7
|
|||
|
|||
Hello, I’m using ArviLaanemets solution here (FillColumnsExample.xlsm) and it is working well. I want to add a macro where if the content of a cell in column C is cleared then cells A, B and D in that row are cleared and all of the records in A,B,C and D below are moved up. I do not want to delete the whole row because of the formulas that are in columns F,G,H and I. I was going to move all the formula cells to another sheet so I could just delete the whole row but it is very convenient having them on the same sheet.
|
#8
|
|||
|
|||
I’m sorry I should’ve started a new post in Excel programing. I will do that now.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
LOOKUP Formula error but WHY??? | Haga | Excel | 1 | 04-17-2019 11:13 PM |
N/A Value in Lookup Formula | NickFazer | Excel | 2 | 04-02-2019 04:22 AM |
Need help with lookup formula | tristanlau | Excel | 1 | 08-14-2017 07:16 AM |
Formula help please (lookup across multiple sheets) | froggybsb03 | Excel | 2 | 03-16-2017 02:16 AM |
How to search with column heading then doing the lookup action for below code | cmreddy.vba | Excel Programming | 0 | 01-20-2015 03:44 AM |