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

Tantalus

New Member
Joined
Aug 27, 2012
Messages
7
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
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
    ABCD
    1DateItemAmountType
    202-MayRent-300Expense
    304-MayPhone Bill-25Expense
    410-MayCar Insurance-30Expense
    515-MayTV Sale200Income
    621-MaySalary1234Income
    728-MayFood Shop-45Expense
    804-JunRent-300Expense
    910-JunElectricity Bill-12Expense
    1015-JunWater Bill-5Expense
    1122-JunFood Shop-28.43Expense
    1223-JunSalary1234Income

    <tbody>
    </tbody>
    Excel 2010

    Worksheet Formulas
    CellFormula
    D2=IF(SIGN(C2)=1,"Income","Expense")

    <tbody>
    </tbody>

    <tbody>
    </tbody>
  • 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.
 

Tantalus

New Member
Joined
Aug 27, 2012
Messages
7
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-known Member
Joined
Aug 13, 2011
Messages
2,932

ADVERTISEMENT

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

Well-known Member
Joined
Jul 15, 2012
Messages
1,953
Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,310
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMN
1DateItemAmountMayJuneJuly
202-May-12Item 1-300May1ItemIncomeExpenseItemIncomeExpenseItemIncomeExpense
304-May-12Item 225May21Item 1 300Item 2301Item 31.86
410-May-12Item 380May32Item 225Item 325Item 423.28
515-May-12Item 4-2May43Item 380Item 7200Item 516
621-May-12Item 5-352May54Item 42Item 850
728-May-12Item 633May65Item 5352Item 1247
804-Jun-12Item 2301June16Item 633
910-Jun-12Item 325June27
1015-Jun-12Item 7-200June38
1122-Jun-12Item 8-50June49
1223-Jun-12Item 1247June510
1303-Jul-12Item 31.86July111
1409-Jul-12Item 4-23.28July212
1514-Jul-12Item 516July313
16
Tables
 

Tantalus

New Member
Joined
Aug 27, 2012
Messages
7
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,413
Messages
5,528,632
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top