| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!! ARRANGE DATA MONTHWISELY IN ROWS
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP!! ARRANGE DATA MONTHWISELY IN ROWS
are u a new excel user ?
"sajay" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP!! ARRANGE DATA MONTHWISELY IN ROWS
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 On Nov 18, 8:28 pm, driller wrote: are u a new excel user ? "sajay" wrote: 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- Hide quoted text -- Show quoted text - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP!! ARRANGE DATA MONTHWISELY IN ROWS
"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. "sajay" wrote: 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 On Nov 18, 8:28 pm, driller wrote: are u a new excel user ? "sajay" wrote: 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- Hide quoted text -- Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP!! ARRANGE DATA MONTHWISELY IN ROWS
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 InsertNameDefine Data Refers to =OFFSET(&A&1,0,0,COUNTA($A:$A),3) Then DataPivot TablesNextRange =DataFinish 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/f...es/pivot1.html http://www.edferrero.com/Tutorials.aspx -- Regards Roger Govier "sajay" wrote in message oups.com... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP!! ARRANGE DATA MONTHWISELY IN ROWS
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 with @tiscali.co.uk "sajay" wrote in message oups.com... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP!! ARRANGE DATA MONTHWISELY IN ROWS
driller,
I have another question in the waiting list. but that seems tougher than this one!! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP!! ARRANGE DATA MONTHWISELY IN ROWS
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP!! ARRANGE DATA MONTHWISELY IN ROWS
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. -- Regards Roger Govier "sajay" wrote in message oups.com... 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP!! ARRANGE DATA MONTHWISELY IN ROWS
PLS FIND ANOTHER TOPIC AND HELP ME
"HELP!! ARRANGE MULTIPLE ROWS IN A SINGLE ROW " YOurs sajay On Nov 20, 4:31 pm, "Roger Govier" wrote: 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. -- Regards Roger Govier "sajay" wrote in ooglegroups.com... 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- Hide quoted text -- Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Getting all rows of data that have a value in a particular column | Excel Discussion (Misc queries) | |||
| resetting last cell | Excel Discussion (Misc queries) | |||
| combining multiple rows of data into one single row of data | Excel Worksheet Functions | |||
| Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
| Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Linear Mode
