Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
bevpike
 
Posts: n/a
Default 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
  #2   Report Post  
Gary Brown
 
Posts: n/a
Default

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

  #3   Report Post  
Biff
 
Posts: n/a
Default

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
.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert rows based on specific value bob Excel Worksheet Functions 6 February 29th 08 08:11 PM
How do I import data into Excel that exceeds 70000 rows? Claudia Phelps Excel Discussion (Misc queries) 2 March 26th 05 12:33 AM
excel formula BONNY Excel Discussion (Misc queries) 1 January 13th 05 06:03 PM
Excel 2003 - Formula result shows as 0:00 Serena Excel Worksheet Functions 4 November 11th 04 11:18 PM
Formula not updating Paul Excel Worksheet Functions 2 November 6th 04 02:05 AM


All times are GMT +1. The time now is 10:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"