3 rows acting as 1 record in excel

[nqueen]

Hi,
my excel sheet looks like this
A B C
1 cie name product status jan04 feb04... (through 2007)
2 X XZ shipped 1 3
3 order 5 6
4 delta X X
5 Z YY shipped X X
3 order X X
4 delta X X
7....


So row 1 to 4 are related to the first company (X) and so on for other companies. I would like to be able to sort these companies the way I want by taking each block (row 1 to 4) as 1 record. Is there any way to let each block of company behave as 1 record?

please I would like help as soon as possible.
thanks in advance!

 

[xlbo]

Without duplicating the cie name filed for every row, you're pretty stuck without code (VBA)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[nqueen]

I am willing to use VBA it s just that I don t know what to do. I could duplicate the company name for every row but how would I make the block act like 1 record? Like how to put a unique key for each block of rows?

 

[xlbo]

Normally, you would use a name / id number plus a date to identify unique records if you have multiple records for each name.......given your data layout however, it would indicate that you should only have 3 rows for each co. so simply duplicating the (cie) name should be enough

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[nqueen]

To be clearer, let me give an example. Let say I want company Z to be sorted before company X, so I want the whole block (the 4 rows of company X) to go down and the other block to go up. But actually, each row is 1 record. I want each block to be 1 record so I can move them as I like... I don t know if I am clear enough...

 

[nqueen]

if someone could please answer I would appreciate.

 

[combo]

If you can arrange your data in source table with header (better replace month string by live date value, as 1/1/2004):

cie name product status month

the pivot table seems to be a good tool to prepare output report on separate page.

combo

 

[xlbo]

surely this is just about sort orders

If you want XZ before X, just sort DESCENDING

If you want a different column to sort in a different way, that is no problem, just make cie name the 1st Sort and whatever column you want to sort after that the 2nd sort.

I really don't see what the issue is....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[nqueen]

I think that I am not clear enough. I ll try to explain again what I would like (sorry for that it s hard for me to explain it is so confusing …)
This is my worksheet:

A B C D E …
1 cie name product status jan04 feb04... (through 2008)

2 Daly XZ shipped 1 3
3 order 5 6
4 delta 4 5
5 Sew YY shipped 4 3
6 order 5 6
7 delta 8 5

Row 1 is used for headings
Row 2- 4 is related to daly company.
Row 5 -7 related to Sew company
Column D thorugh the end has the dates from jan04 to dec 2008

I don t want to sort ascending or descending. I want to be able to say that Daly will be the last company and XX will be the first company and Sew the second and so on (no matter ascending/descending order). The issue here is that I want to be able to say tha row 2-4 is one record (not 3 records( as per 1 record per row)). So if I want to move a company, the three rows (block) will move together. If each company had one record per row, I would just need to insert a unique key and play with it. In this case how can I make a unique key for a record that is split in three rows?


thanks again for your help!

 

[SkipVought]

As stated before, you need to "expand" your data to include a value in every row for each column.
[tt]
cie name product status Jan-04 Feb-04.....
Daly XZ shipped 1 3
Daly XZ order 5 6
Daly XZ delta 4 5
Sew YY shipped 4 3
Sew YY order 5 6
Sew YY delta 8 5
[/tt]
with you data arranges as above you can sort, filter, use complex lookups etc.

I fyou only want to SEE one row of cie name and product, then use Conditional Formatting to shade the font as WHITE.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[KenWright]

As has been said, the only way you will do is is to give each block of records a unique id. One way as follows:-

On a copy of your file:-
-----------------------
First off create a copy of your CIE column perhaps on another sheet (I'll assume sheet1). Select all of that column, do edit / Go to special / blanks, then do Edit / delete / shift cells up. That gets rid of the blanks and gives you a list of your companies. Now in the column to the right of this, put a set of numbers in the order you would like the records to appear, eg 2,3,5,1,6,7,8.... etc

Now on your data sheet, lets first fill in the blanks on the company, so select the CIE column from the first entry to the last row of data, do edit / Go to / special / blanks, type = and then hit CTRl+ENTER at the same time to enter the data. You should now have filled in the blanks, so copy that column and paste special as values to get rid of the formulas.

Insert a column before your CIE column, put a 1,2,3 against the first record's three rows and also the second record's 3 rows, then just fill down. Title the column ID2. It should look like:-

1
2
3
1
2
3
1
2
3
...... etc

Now, assuming that your helper table on sheet1 is in say A1:B100, back on your data sheet, insert another column before ID2 and label it ID1. Then, assuming that the first record is in row 2, CIE value is in C2 and ID2 is in B2, in cell A2 put the following formula and copy down to the end:-

=VLOOKUP(C2,Sheet1!$A$1:$B$100,2,0)

Now just sort your data sheet on Cols 1 and 2 in ascending order, and you will get the order you specified in your helper table.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[nqueen]

ok let say I expand my data as you showed me Skip. Now I want All 3 Daly rows to be treated as one record.
as if my real record would be:

Daly XZ jan04_shipped shipped_feb04...Order_jan04 order_fb04

I tried to do it this way but the record was way too long so excel was giving me an error saying out of boundries or something like that.

Let s forget about sorting or filtering. I need to treat three related rows as 1 record in some way. how?

 

[Gavona]

To fill the blanks as Skip suggested:
"If you have everything you need in the table with the sole exception of the fact that it doesn't repeat the values of the row fields, then you can simply select the entire table, do Edit / Go to / Special / Blanks, hit = and then the UP arrow once, hit CTRL+ENTER and your data will be as you wanted. (thank Ken Wright for that tip).

To sort in your customised order:
How about creating a table in a separate sheet containing all the "cie name" values in the first column and the order you want them in in the second.
Then add a column to your database "sortorder" use vlookup to populate this. (if you have lots of data then copy, pastespecial to values)
Then sort your data using the new column, or dispay using a pivot table.

In your example it appears that the data would remain in the right order within a record either naturally, or if doing lots of sorting by sorting on "sortorder" and then "status" (descending). Alternatively you could refine the formula for the sortkey to add the row number within each record - possibly using the structure =cienamelookup&statuslookup

Thanks,

Gavin

 

[nqueen]

Thanks a lot guys for your help! I tried KenWright's advice and it sort of works. Now I just need to write a macro to automate it because the file is formated in a certain style.

thanks again!