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

[monicamarie]

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/Row	A	B	C	D
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​

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F
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
Sheet3

Cell Formulas
Range	Formula
B1:F19	B1	=LET(a,OFFSET($A1,COLUMNS($B:B),0),IF(OR(ISNUMBER($A1),A1=""),"",IF(ISNUMBER(a),a,"")))

 

[monicamarie]

=LET(a,OFFSET($A1,COLUMNS($B:B),0),IF(OR(ISNUMBER($A1),A1=""),"",IF(ISNUMBER(a),a,"")))

Thank you! You're a Life Saver! This formula works perfect.

 

[Herakles]

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/Row	A	B	C	D
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.

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

 

[Herakles]

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/Row	A	B	C	D
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?