Sorting a list.

[shyam]

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
	A	B	C	D	E	F	G	H	I	J
1	Sr.No.	Period	Account No.	MDN No.	User	Feb-2009
2						Local	STD	ISD	Total	Deduction to be made
3	1	01/12/08 to 31/12/08	2814957595	1111111	RRR	736.80	58.00	0.00	1345.29	345.29
4	2			2222222	KKK	266.70	40.00	0.00	807.53	0.00
5	3			33333333	CCC	238.50	78.00	0.00	869.67	0.00
6	4			4444444	LLL	220.80	0.00	0.00	703.15	0.00
7	5			5555555	GGG	288.60	11.00	0.00	949.55	0.00
8
9	1	01/12/08 to 31/12/08	2820407703	66666666	AAA	187.20	182.00	0.00	864.83	0.00
10	2			77777777	BBB	187.20	182.00	0.00	864.83	0.00
11
12	1	01/12/08 to 31/12/08	2820407705	77777777	FFF	200.00	500.00	0.00	200.00	0.00
13	2			88888888	QQQ	200.00	500.00	0.00	200.00	0.00
Sheet1

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
Shyam

 

[Yard]

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.

 

[Andrew Poulsom]

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.

 

[shyam]

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
Shyam

 

[Yard]

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....

 

[Andrew Poulsom]

You could format the cells you don't want to see with a white font.

 

[Yard]

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)

 

[shyam]

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,,

Shyam

 

[Yard]

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

=INDIRECT("R[-1]C",0)

Set the format to a white font.

Click OK.

 

[shyam]

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 ?

tks

Shyam