Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
How do I import data into Excel that exceeds 70000 rows? | Excel Discussion (Misc queries) | |||
excel formula | Excel Discussion (Misc queries) | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions | |||
Formula not updating | Excel Worksheet Functions |