How to arrange a data range into a new table based on multiple criteria?

[Tantalus]

Hi all,

I have a problem whereby I have a list of data that has a date, a transaction and a balance. I want to be able to rearrange this data into multiple tables, one for each month.

The pictures below explain the situation better, on the left is the list of data I need to sort, and on the right is how I'd like the data displayed. So for the May columns I would like to display all transactions that happened in May and, depending on whether it is an income or an expense, the amount in the corresponding column.

i.e. so the finish result looks something like this:

Any help would be much appreciated

 

[circledchicken]

Hi and welcome to the forum,

Perhaps try using a PivotTable. One way might be:

• Add an extra column to the end of your dataset called 'Type' like this (dragging down the formula in D2):
  
  Sheet1[TABLE="class: html-maker-worksheet"]
  <tbody>[TR]
  [TH][/TH]
  [TH]A[/TH]
  [TH]B[/TH]
  [TH]C[/TH]
  [TH]D[/TH]
  [/TR]
  [TR]
  [TH]1[/TH]
  [TD]Date[/TD]
  [TD]Item[/TD]
  [TD]Amount[/TD]
  [TD]Type[/TD]
  [/TR]
  [TR]
  [TH]2[/TH]
  [TD="align: right"]02-May[/TD]
  [TD]Rent[/TD]
  [TD="align: right"]-300[/TD]
  [TD]Expense[/TD]
  [/TR]
  [TR]
  [TH]3[/TH]
  [TD="align: right"]04-May[/TD]
  [TD]Phone Bill[/TD]
  [TD="align: right"]-25[/TD]
  [TD]Expense[/TD]
  [/TR]
  [TR]
  [TH]4[/TH]
  [TD="align: right"]10-May[/TD]
  [TD]Car Insurance[/TD]
  [TD="align: right"]-30[/TD]
  [TD]Expense[/TD]
  [/TR]
  [TR]
  [TH]5[/TH]
  [TD="align: right"]15-May[/TD]
  [TD]TV Sale[/TD]
  [TD="align: right"]200[/TD]
  [TD]Income[/TD]
  [/TR]
  [TR]
  [TH]6[/TH]
  [TD="align: right"]21-May[/TD]
  [TD]Salary[/TD]
  [TD="align: right"]1234[/TD]
  [TD]Income[/TD]
  [/TR]
  [TR]
  [TH]7[/TH]
  [TD="align: right"]28-May[/TD]
  [TD]Food Shop[/TD]
  [TD="align: right"]-45[/TD]
  [TD]Expense[/TD]
  [/TR]
  [TR]
  [TH]8[/TH]
  [TD="align: right"]04-Jun[/TD]
  [TD]Rent[/TD]
  [TD="align: right"]-300[/TD]
  [TD]Expense[/TD]
  [/TR]
  [TR]
  [TH]9[/TH]
  [TD="align: right"]10-Jun[/TD]
  [TD]Electricity Bill[/TD]
  [TD="align: right"]-12[/TD]
  [TD]Expense[/TD]
  [/TR]
  [TR]
  [TH]10[/TH]
  [TD="align: right"]15-Jun[/TD]
  [TD]Water Bill[/TD]
  [TD="align: right"]-5[/TD]
  [TD]Expense[/TD]
  [/TR]
  [TR]
  [TH]11[/TH]
  [TD="align: right"]22-Jun[/TD]
  [TD]Food Shop[/TD]
  [TD="align: right"]-28.43[/TD]
  [TD]Expense[/TD]
  [/TR]
  [TR]
  [TH]12[/TH]
  [TD="align: right"]23-Jun[/TD]
  [TD]Salary[/TD]
  [TD="align: right"]1234[/TD]
  [TD]Income[/TD]
  [/TR]
  </tbody>[/TABLE]
  Excel 2010
  
  

  Cell	Formula
  D2	=IF(SIGN(C2)=1,"Income","Expense")

  
  <tbody>
  [TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
  <tbody>[TR]
  
  </tbody>
  [/TD]
  [/TR]
  </tbody>[/TABLE]
• Select the whole range of data (including the column headers) and insert a PivotTable
• Drag Date into the Column Labels
• Drag Type into the Column Labels
• Drag Item into the Row Labels
• Drag Amount into the Values area
• Select the first date in the Column Labels, choose Group Field and select Months
• Remove SubTotals

Note that this won't be exactly as you wanted as the row labels are only displayed once on the left. Filtering by month will then give you just the items for each month.

 

[patel45]

can you attach a link to your file ?

 

[Tantalus]

can you attach a link to your file ?

Here you go: Excel File


Thanks for your replies, ideally I'd like to avoid pivot tables if possible.

 

[circledchicken]

Here you go: Excel File


Thanks for your replies, ideally I'd like to avoid pivot tables if possible.

Is there a particular reason you want to avoid PivotTables?

 

[Tantalus]

Is there a particular reason you want to avoid PivotTables?

Well mainly for my own asthetic reasons, but also it makes it easier for other calculations using the data and that the sheet will need to be used with older versions of excel (and i'm not sure if pivot tables would work?)

Plus I do like a good formula...

 

[circledchicken]

Well mainly for my own asthetic reasons, but also it makes it easier for other calculations using the data and that the sheet will need to be used with older versions of excel (and i'm not sure if pivot tables would work?)

Plus I do like a good formula...

Ok, hopefully someone can help with a formula solution.

In case it helps in the future and you haven't already used PivotTables much before, here are some resources you might find useful on learning about them. They are very easy, flexible and useful in many situations. I think they should be compatible with older versions of Excel (not sure how far back, although at least 2002):

Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one | Chandoo.org - Learn Microsoft Excel Online
Excel Pivot Table -- Dynamic Data Source

 

[patel45]

Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
DR = 3
Dcol = 5
prevmonth = 5
For j = 2 To LR
  Item = Cells(j, 2).Value
  amount = Cells(j, 3).Value
  amonth = Month(Cells(j, 1).Value)
  If amonth = prevmonth + 1 Then
    DR = 3
    prevmonth = amonth
  End If
  If amount >= 0 Then
    col = 0
  Else
    col = 1
  End If
  Cells(DR, Dcol + (amonth - 5) * 3).Value = Item
  Cells(DR, Dcol + (amonth - 5) * 3 + 1 + col).Value = Abs(amount)
  DR = DR + 1
Next
End Sub

 

[Peter_SSs]

Here's a possible formula approach. I've used columns D & E as helper columns.

Formula in D2 is copied down.
Column E is is populated manually.
Formulas in F3:H3 copied down. Then F3:H?? can be copied and pasted to I3, L3 etc

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Item	Amount			May			June			July
2	02-May-12	Item 1	-300	May1		Item	Income	Expense	Item	Income	Expense	Item	Income	Expense
3	04-May-12	Item 2	25	May2	1	Item 1		300	Item 2	301		Item 3	1.86
4	10-May-12	Item 3	80	May3	2	Item 2	25		Item 3	25		Item 4		23.28
5	15-May-12	Item 4	-2	May4	3	Item 3	80		Item 7		200	Item 5	16
6	21-May-12	Item 5	-352	May5	4	Item 4		2	Item 8		50
7	28-May-12	Item 6	33	May6	5	Item 5		352	Item 1	247
8	04-Jun-12	Item 2	301	June1	6	Item 6	33
9	10-Jun-12	Item 3	25	June2	7
10	15-Jun-12	Item 7	-200	June3	8
11	22-Jun-12	Item 8	-50	June4	9
12	23-Jun-12	Item 1	247	June5	10
13	03-Jul-12	Item 3	1.86	July1	11
14	09-Jul-12	Item 4	-23.28	July2	12
15	14-Jul-12	Item 5	16	July3	13
16
Tables

 

[Tantalus]

Thank you for all your replies, you've all been really helpful. I decided to use your method Peter as it best fits what I wanted to achieve, and it's safe to say that it would've taken me a considerable amount of effort and time to come up with that so thanks :P.