ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Updating rows - Excel Formula or Something Else? (https://www.excelbanter.com/excel-worksheet-functions/19712-updating-rows-excel-formula-something-else.html)

bevpike

Updating rows - Excel Formula or Something Else?
 
I need to update a set of rows in an Excel worksheet. Here is the example:

*********************
ColA ColB
265004357
Wife
Daughter
Son
Son

265089764
Child
Child
Husband

265098308
Spouse
Daughter
Daughter
*******************

I need to insert the unique number at the beginning of a group of rows (each
unique group is separted by a blank row) into colB and skip to the new uique
number when the group changes. Here is what the data should look like:

*********************
ColA ColB
265004357 265004357
Wife 265004357
Daughter 265004357
Son 265004357
Son 265004357

265089764 265089764
Child 265089764
Child 265089764
Husband 265089764

265098308 265098308
Spouse 265098308
Daughter 265098308
Daughter 265098308
*******************

Can I do this with an Excel formula or do I need to use something else (eg.
VB)?

Please let me know.

Thanks in advance,

Attila

Gary Brown

insert a row immediately below 'ColA ColB' so there is a blank row
Assuming the 1st SSN (265004357) begins at cell A2 and there is a blank row
between each data set, in B2 put...
=IF(LEN(B1)=0,A2,IF(LEN(A2)=0,"",B1))
and copy it down.

HTH,
Gary Brown


"bevpike" wrote:

I need to update a set of rows in an Excel worksheet. Here is the example:

*********************
ColA ColB
265004357
Wife
Daughter
Son
Son

265089764
Child
Child
Husband

265098308
Spouse
Daughter
Daughter
*******************

I need to insert the unique number at the beginning of a group of rows (each
unique group is separted by a blank row) into colB and skip to the new uique
number when the group changes. Here is what the data should look like:

*********************
ColA ColB
265004357 265004357
Wife 265004357
Daughter 265004357
Son 265004357
Son 265004357

265089764 265089764
Child 265089764
Child 265089764
Husband 265089764

265098308 265098308
Spouse 265098308
Daughter 265098308
Daughter 265098308
*******************

Can I do this with an Excel formula or do I need to use something else (eg.
VB)?

Please let me know.

Thanks in advance,

Attila


Biff

Hi!

Here's one way:

Assume your data in column A starts in cell A1. In B1
enter this array formula using the key combo of
CTRL,SHIFT,ENTER:

=IF(A1="","",INDEX(A$1:A1,LARGE(IF(ISNUMBER(A$1:A1 ),ROW
(A$1:A1)),ROW($1:$1))))

Copy down as needed. I'm assuming that the numbers
represented in your data are actually numbers and not text.

Biff

-----Original Message-----
I need to update a set of rows in an Excel worksheet.

Here is the example:

*********************
ColA ColB
265004357
Wife
Daughter
Son
Son

265089764
Child
Child
Husband

265098308
Spouse
Daughter
Daughter
*******************

I need to insert the unique number at the beginning of a

group of rows (each
unique group is separted by a blank row) into colB and

skip to the new uique
number when the group changes. Here is what the data

should look like:

*********************
ColA ColB
265004357 265004357
Wife 265004357
Daughter 265004357
Son 265004357
Son 265004357

265089764 265089764
Child 265089764
Child 265089764
Husband 265089764

265098308 265098308
Spouse 265098308
Daughter 265098308
Daughter 265098308
*******************

Can I do this with an Excel formula or do I need to use

something else (eg.
VB)?

Please let me know.

Thanks in advance,

Attila
.



All times are GMT +1. The time now is 12:15 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com