Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
grouping rows with blank cells
I have a worksheet with data like:
Name ID Attribute Attribute Name ID Attribute Attribute Attribute Name ID Attribute Each Name and ID can have multiple attributes (lines), up to about 20 lines per person. Only the first line for each person shows the Name and ID. Is there an easy way to "group" the lines together in such a way that I could sort on Name or ID and not lose the Attributes (in the correct order) that go with them? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
grouping rows with blank cells
In an empty column adjacent to your data, use a formula such as this (assumes
Name is in column B, starting in row 2). =if(b2="",b1,b2) copy the formula down for all the rows, then select all these formulas, use EditCopy followed by EditPaste SpecialValues to convert this into a new name column. Next, number all the rows from 1 on up Now sort by the new Name column and then by the new number column "Emmweb" wrote: I have a worksheet with data like: Name ID Attribute Attribute Name ID Attribute Attribute Attribute Name ID Attribute Each Name and ID can have multiple attributes (lines), up to about 20 lines per person. Only the first line for each person shows the Name and ID. Is there an easy way to "group" the lines together in such a way that I could sort on Name or ID and not lose the Attributes (in the correct order) that go with them? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
grouping rows with blank cells
Emmweb wrote...
I have a worksheet with data like: Name ID Attribute Attribute Name ID Attribute Attribute Attribute Name ID Attribute Each Name and ID can have multiple attributes (lines), up to about 20 lines per person. Only the first line for each person shows the Name and ID. Is there an easy way to "group" the lines together in such a way that I could sort on Name or ID and not lose the Attributes (in the correct order) that go with them? The easiest way to sort such tables is to use an additional column of formulas as the sort key. If your table above were in A1:C6, enter the following formulas. D1: =A1&TEXT(ROW(),"\.00000") D2: =IF(A2<"",A2&TEXT(ROW(),"\.00000"),SUBSTITUTE(D1, ROW()-1,ROW())) Fill D2 down as needed. Sort on column D to sort the table by name. Replace col A references with col B references to sort by ID. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
grouping rows with blank cells
We're actually dealing with a whole bunch of spreadsheets of varying data in
this type of format. The "name" or identifier row, could be different in all of them - meaning in a different column. And what we're hoping to do is set up one macro that can be used on all of the sheets. I'm thinking with the built-in filter and sort and lists in Excel, surely there's a built in ability for something this common? At least, I was hoping. But thank you, Duke. Your suggestion will get us through until we can find something we can use on a broader scale. "Duke Carey" wrote: In an empty column adjacent to your data, use a formula such as this (assumes Name is in column B, starting in row 2). =if(b2="",b1,b2) copy the formula down for all the rows, then select all these formulas, use EditCopy followed by EditPaste SpecialValues to convert this into a new name column. Next, number all the rows from 1 on up Now sort by the new Name column and then by the new number column "Emmweb" wrote: I have a worksheet with data like: Name ID Attribute Attribute Name ID Attribute Attribute Attribute Name ID Attribute Each Name and ID can have multiple attributes (lines), up to about 20 lines per person. Only the first line for each person shows the Name and ID. Is there an easy way to "group" the lines together in such a way that I could sort on Name or ID and not lose the Attributes (in the correct order) that go with them? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CSV file with 13space characters in blank cells | Excel Discussion (Misc queries) | |||
CONCATENATE problem with blank cells | Excel Discussion (Misc queries) | |||
Blank cells represented in area charts | Charts and Charting in Excel | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
How to delete blank rows | Excel Discussion (Misc queries) |