HELP!! ARRANGE DATA MONTHWISELY IN ROWS

[sajay]

Dear Frinds,

Pls hlp
I have a situation where by i get only monthly trial balance like this
HEAD APRIL
HEAD 1 44,583.00
HEAD 2 1,405.00
HEAD 3 15,756.00
HEAD 4 17,099.31
HEAD 5 23,492.45

and next month
HEAD MAY
HEAD 1 11022
HEAD 2 545644
HEAD 3 345454
HEAD 4 154547

I want to arrange data like this
HEAD APRIL MAY
HEAD 1 44,583.00 11022
HEAD 2 1,405.00 545644
HEAD 3 15,756.00 345454
HEAD 4 17,099.31 154547
HEAD 5 23,492.45

I tried this
copied the April file to an excel sheet
copied the May file to another excel sheet
created a cloumn in may after the head column
copied the May sheet to April sheet
so it looked like this

HEAD APRIL MAY
HEAD 1 44,583.00
HEAD 2 1,405.00
HEAD 3 15,756.00
HEAD 4 17,099.31
HEAD 5 23,492.45
HEAD 1 11022
HEAD 2 545644
HEAD 3 345454
HEAD 4 154547

then sorted with head as key
but how to make amounts in a single row of months for head 1 etc. is a
real head ache
i have to cut paste and del
just for imformation i have almost 700 heads in one section and all
togather 4 subsectino
like general ledger and clients ledger and suppliers ledger and cash &
banks



is there any way to do this with out VBA?/
is there any way to do this with VBA

 

[Guest]

are u a new excel user ?

 

[sajay]

do dear driller

BUT AM NOT THAT INCLINED TO VBA
I KNOW LITTLE BUT OF VB (LITTLE BIT)
Usually i use the excel functions to make my work easier. but yet to
find one for this

 

[Guest]

"general ledger and clients ledger and suppliers ledger and cash & banks"
oooops...sorry sajay, i forgot to read the above. probably you can page
other bright guys to do some acrobatics for this purpose...try again for
another question thread.
good luck.

 

[Roger Govier]

Hi

One way
1. On a new sheet, set up headings in A1:C1 of Heading, Amount and
Month.
2. Copy A2:Bnnn where nnn is the last row number from the sheet marked
April and paste to A2 of your new sheet.
Copy B1 from April, i.e. the Month name and paste to C2:Cnn so that the
Month name is alongside each entry.

Repeat procedure 2 for as many months as you have data, pasting the
entries immediately below those already entered.

On this new Master sheet create a named range by Insert>Name>Define >
Data

Refers to =OFFSET(&A&1,0,0,COUNTA($A:$A),3)

Then Data>Pivot Tables>Next>Range =Data>Finish
Drag Heading to the Row area
Drag Month to the column area
Drag Amount to the Data area.

As you add more data each month, below the last entry ion your data
sheet, the range named Data will expand automatically to include this
new data, and clicking Refresh on the Pivot Table will incorporate it as
a new column.

For more help on Pivot tables take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/flashfiles/pivot1.html

http://www.edferrero.com/Tutorials.aspx

 

[Sandy Mann]

sajay.

You could do this with a pivot table:

With each months data in a separate sheet select Data > Pivot Table Report >
Multiple Consolidation Ranges > Next > I will create the page fields > Next

In the 'Step 2b of 4' dialog box click into the 'Range' box to activate it
then highlight the range in the first sheet, click the Add button then
repeat the process until you have all the sheets ranges in the "All Ranges"
box. Leave the "How many page fields do you want?" selected at zero. Then
select Next

If the box in the Data fiels is saying "Count of data" double click on it
and select "Sum" in the PivotTable Field dialog box then select OK and then
Next.

In Step 4 select New worksheet or existing worksheet as you require. then
select Finish.

If you are adding more months later then select any cell in the pivot table
with a right-click and then select Wizard. Go back to Step 2b and add the
new data range as before. If you are updating the existing Pivot table then
make sure that you select both the sheet and upper left-hand cell of the
existing pivot Table.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[sajay]

driller,
I have another question in the waiting list.
but that seems tougher than this one!!

 

[sajay]

dear roger and sandy man

both of you are referring to Pivot tables.
well I will certainly try it
but don't there have any other options??
what i did was as decribed earlier
and put a formula in the D column

IF(EXACT(A2,A3), C3, "") \\=if(exact(cell1, cell2), cell2's amount,
blank)
and deleted the duplicate cells by putting another formula in E column
IF(EXACT(A2,A3), "$$", "")
and sorting with that cell
but every month doing the same things is quite borring
so i thougt there will be some easy ways (as the methods are same for
all months)
i do believe that VBA can do a long way.


YOurs,
sajay

 

[Roger Govier]

Hi

Of course the problem can be solved writing code with VBA if that is
what you want to do.
In my opinion, the Pivot Table approach provides the easiest solution.

Another alternative, would be to set up a Master sheet with your
Headings starting in A2 and going down column A.
In B1 through M1 enter the Months Apr, May etc.

Each month, copy your data to a new sheet within the Workbook, and
rename it to the relevant Month Name.

In cell B2 of the Master sheet enter
IF(A2="","",
IF(ISERROR(VLOOKUP(A2,INDIRECT("'"&B$1&"'!A:B"),2,0)),"",
VLOOKUP(A2,INDIRECT("'"&B$1&"'!A:B"),2,0)))

Copy across through C2:M2
Copy B2:M2 down for as many rows as you have headings in your Master
sheet.

 

[sajay]

PLS FIND ANOTHER TOPIC AND HELP ME

"HELP!! ARRANGE MULTIPLE ROWS IN A SINGLE ROW "

YOurs
sajay