Excel - Sorting with muliple line

[lmcate2]

I have a report in Excel I need to sort by Company Name each of the components is 5 lines. Is there an easy way to sort them. Thanks,

Column A Column B Column C
1 Company Name
2 blank line
3 total orders 34 1
4 Total rev $500.00 2
5 Blank Line
6 Company Name
7 blank line
8 total orders 34 1
9 Total rev $40.00 2
10 blank line

 

[SkipVought]

Hi,

What is the data is column C?

The FIRST objective shoud be to get your data into a TABLE FORAMT like this
[tt]
Company Name Total Orders Total Rev
ACB, Inc 34 500.00
XYZ Corp 34 40.00
[/tt]
IF IF IF...

your data is ALWAYS ALWAYS ALWAYS 5 rows JUST AS YOU DESCRIBED...

Insert a blank row above your data. THIS SOLUTION ASSUMES THAT YOUR DATA BEGINS IN A2.

These heading in COLUMN G ROW 1
[tt]
Company Name Total Orders Total Rev
[/tt]
this formula in G2 and copy down.
[tt]
=IF(MOD(ROW()-2,5)=COLUMN()-7,$A2,G1)
[/tt]
this formula in H2 and copy ACROSS 2 ROWS and down.
[tt]
=IF(G2<>G1,"",IF(MOD(ROW()-2,5)=COLUMN()-7,$A2,H1))
[/tt]
Now copy the entire area where your formulas are

Edit/paste Special - paste VALUES.

NOW...

Delete the COLUMNS with no data. This leaves you with THREE columns of data.

Copy this table to a new sheet.

AutoFilter on blanks in each column and DELETE ROWS containing blanks.

NOW...

you can sort by Company Name.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[Zathras]

Skip is absolutely correct, of course. But if you don't have the luxury of re-designing your spreadsheet, here is one way to accomplish what you want:

1. Insert a new column A.
2. Put this formula in cell A1:[tt]
=OFFSET(B1,-MOD(ROW(B1)-1,5),0)&(10000+ROW())[/tt]
3. Copy column A and paste special/values.
4. Sort on column A
5. Delete column A.

This assumes your data begin in cell A1 exactly as indicated in your post. If that is not the case, then some tweaking of the formula will be required.

BTW, welcome back Skip. Missed you. Been on vacation?