Sorting a list.


Active Member
Jul 18, 2005
I have a sheet wherein I have statement period, Account No. MDN No., User Names and the other related details as given in the statement below :

Excel Workbook
1Sr.No.PeriodAccount No.MDN No.UserFeb-2009
2LocalSTDISDTotalDeduction to be made
3101/12/08 to 31/12/0828149575951111111RRR736.8058.000.001345.29345.29
9101/12/08 to 31/12/08282040770366666666AAA187.20182.000.00864.830.00
12101/12/08 to 31/12/08282040770577777777FFF200.00500.000.00200.000.00

The statement period is common and under one Account no. I have different MDN Nos and user names. Now when I am sorting the sheet under MDN No. or User Name., How do I avoid mixing up the related details ?

Any help on this will be highly appreciated.

Tks n rgds

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Easiest way is just to replicate the period and account numbers in the blank cells. Not good practice to have a database like this with missing entries.
Upvote 0
Repeat the Period and Account No in the blank cells.

To do this quickly select columns B:C and choose Edit|Go To|Special|Blanks|OK. In the Formula Bar type =, press the up arrow and press Ctrl+Enter (that's hold down Ctrl and press Enter). You can convert the formulas to values using Copy/Paste Special|Values.
Upvote 0
Mr. Andrew

Thanks for your reply. The job was made very simpler with your suggestion.

But if I want to present the statement as per the format of my statement, i.e. without repeating the statement period and account no. one below the other, then how would one do it without making it less congested.

The main intention is to list the details of the people who are falling under a particular Account No.

OR do you have any other option as to how to go about typing data in such statements.

Tks n rgds
Upvote 0
If you use your data table as a proper data table (by filling in the blanks as above), then you could use a Pivot Table to function as a report based on that data table. This gives you much more easy flexibility over the layout - you can drag and drop fields to report on different levels, and you can use filters to report on certain types of items etc..etc....
Upvote 0
Or to make it more flexible, use Conditional Formatting with a white font with the condition...e.g. in cell A2:

Cell value is equal to =INDIRECT("R[-1]C",0)
Upvote 0
Mr. Andrew,, That's exactly what was in my mind also. i.e. to format with a White Font.

Mr. Yard, I didn't get your example. (sorry for my ignorance on the matter) You give this formula under which option in conditional formating : =INDIRECT("R[-1]C",0). My statement starts from the cell A3.. Could you please elaborate on the matter.

Many Thanks,,

Upvote 0
Select the area within which you want to "blank out" the duplicate values.

Go to Conditional Formatting.

(In Excel 2007, select New Rule, then Format only cells that contain).

Choose Cell value equal to

In the next (blank) box, enter


Set the format to a white font.

Click OK.
Upvote 0
Thanks for your reply... I got it...

One last question, what does the R, -1 and C in your formula =INDIRECT("R[-1]C",0) refers to ?


Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back