Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-23-2022, 04:26 PM
14spar15 14spar15 is offline Problems with lookup/search Formula Windows 7 64bit Problems with lookup/search Formula Office 2010 64bit
Advanced Beginner
Problems with lookup/search Formula
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default 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
Attached Files
File Type: xlsm Book2.xlsm (10.2 KB, 10 views)
Reply With Quote
  #2  
Old 10-23-2022, 09:03 PM
14spar15 14spar15 is offline Problems with lookup/search Formula Windows 7 64bit Problems with lookup/search Formula Office 2010 64bit
Advanced Beginner
Problems with lookup/search Formula
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Would VBA or some sort of macro be better?
Reply With Quote
  #3  
Old 10-23-2022, 11:38 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Problems with lookup/search Formula Windows 10 Problems with lookup/search Formula Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #4  
Old 10-24-2022, 01:07 AM
ArviLaanemets ArviLaanemets is offline Problems with lookup/search Formula Windows 8 Problems with lookup/search Formula Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Attached Files
File Type: xlsm FillColumnsExample.xlsm (12.0 KB, 9 views)
Reply With Quote
  #5  
Old 10-24-2022, 05:58 AM
kilroyscarnival kilroyscarnival is offline Problems with lookup/search Formula Windows 10 Problems with lookup/search Formula Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

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.
Reply With Quote
  #6  
Old 10-28-2022, 02:09 PM
14spar15 14spar15 is offline Problems with lookup/search Formula Windows 7 64bit Problems with lookup/search Formula Office 2010 64bit
Advanced Beginner
Problems with lookup/search Formula
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Thanks to all who helped out here. At the moment I am using the ArviLaanemets solution and it seems to be working fine.
Reply With Quote
  #7  
Old 10-31-2022, 01:34 PM
14spar15 14spar15 is offline Problems with lookup/search Formula Windows 7 64bit Problems with lookup/search Formula Office 2010 64bit
Advanced Beginner
Problems with lookup/search Formula
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 10-31-2022, 01:40 PM
14spar15 14spar15 is offline Problems with lookup/search Formula Windows 7 64bit Problems with lookup/search Formula Office 2010 64bit
Advanced Beginner
Problems with lookup/search Formula
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

I’m sorry I should’ve started a new post in Excel programing. I will do that now.
Reply With Quote
Reply

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 09:18 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft