Need to Extract Values from a Column Relative to Text Values Above

monicamarie

Board Regular
Joined
Sep 2, 2008
Messages
72
Office Version
  1. 365
I have a list of names and numbers in Column A. Below each name there is a cell with a number in it that I would like to return in Column B next to the name. The problem is that sometimes there are multiple cells in a row with numbers in them. See example below. I am looking for a way to have Excel look below the cell with the name and when it finds a cell with a number, return it to the adjacent cell above and to keep doing this until it sees a text value (name) and start over. Any help would be greatly appreciated.

Sample below shows desired results in columns B, C & D.



Column/RowABCD
1​
Malloy, Peggy
155​
2​
155​
3​
Wilkins, Julia
184​
4​
184​
5​
Nadeau, Bill
107​
251​
6​
107​
7​
251​
8​
Ferreira, Carlos
110​
9​
110​
10​
Horwitz, Bruce
115​
11​
115​
12​
Ashline, Pam
153​
268​
163​
13​
153​
14​
268​
15​
163​
16​
Heitman, Chris
161​
17​
161​
18​
Tomao, Joe
170​
19​
170​
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Try:

Book1
ABCDEF
1Malloy, Peggy155    
2155     
3Wilkins, Julia184    
4184     
5Nadeau, Bill107251   
6107     
7251     
8Ferreira, Carlos110    
9110     
10Horwitz, Bruce115    
11115     
12Ashline, Pam153268163  
13153     
14268     
15163     
16Heitman, Chris161    
17161     
18Tomao, Joe170    
19170     
Sheet3
Cell Formulas
RangeFormula
B1:F19B1=LET(a,OFFSET($A1,COLUMNS($B:B),0),IF(OR(ISNUMBER($A1),A1=""),"",IF(ISNUMBER(a),a,"")))
 
Upvote 1
Solution
I have a list of names and numbers in Column A. Below each name there is a cell with a number in it that I would like to return in Column B next to the name. The problem is that sometimes there are multiple cells in a row with numbers in them. See example below. I am looking for a way to have Excel look below the cell with the name and when it finds a cell with a number, return it to the adjacent cell above and to keep doing this until it sees a text value (name) and start over. Any help would be greatly appreciated.

Sample below shows desired results in columns B, C & D.



Column/RowABCD
1​
Malloy, Peggy
155​
2​
155​
3​
Wilkins, Julia
184​
4​
184​
5​
Nadeau, Bill
107​
251​
6​
107​
7​
251​
8​
Ferreira, Carlos
110​
9​
110​
10​
Horwitz, Bruce
115​
11​
115​
12​
Ashline, Pam
153​
268​
163​
13​
153​
14​
268​
15​
163​
16​
Heitman, Chris
161​
17​
161​
18​
Tomao, Joe
170​
19​
170​
Try this on a copy og
Thank you! You're a Life Saver! This formula works perfect.
Or some VBA.

VBA Code:
Public Sub subCopy()
Dim rngArea As Range

    For Each rngArea In Range(Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row). _
        SpecialCells(Type:=xlCellTypeConstants, Value:=xlNumbers).Address).Areas
        rngArea.Cells(1).Offset(-1, 1).Resize(1, rngArea.Rows.Count).Value = Application.Transpose(rngArea)
    Next rngArea
        
End Sub
 
Upvote 0
I have a list of names and numbers in Column A. Below each name there is a cell with a number in it that I would like to return in Column B next to the name. The problem is that sometimes there are multiple cells in a row with numbers in them. See example below. I am looking for a way to have Excel look below the cell with the name and when it finds a cell with a number, return it to the adjacent cell above and to keep doing this until it sees a text value (name) and start over. Any help would be greatly appreciated.

Sample below shows desired results in columns B, C & D.



Column/RowABCD
1​
Malloy, Peggy
155​
2​
155​
3​
Wilkins, Julia
184​
4​
184​
5​
Nadeau, Bill
107​
251​
6​
107​
7​
251​
8​
Ferreira, Carlos
110​
9​
110​
10​
Horwitz, Bruce
115​
11​
115​
12​
Ashline, Pam
153​
268​
163​
13​
153​
14​
268​
15​
163​
16​
Heitman, Chris
161​
17​
161​
18​
Tomao, Joe
170​
19​
170​
Do you need to delete the rows with numbers in column A afterwards?
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top