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

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

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

  1. #1
    New Member
    Join Date
    Aug 2012
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

     
    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

  2. #2
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
      A B C D
      1 Date Item Amount Type
      2 02-May Rent -300 Expense
      3 04-May Phone Bill -25 Expense
      4 10-May Car Insurance -30 Expense
      5 15-May TV Sale 200 Income
      6 21-May Salary 1234 Income
      7 28-May Food Shop -45 Expense
      8 04-Jun Rent -300 Expense
      9 10-Jun Electricity Bill -12 Expense
      10 15-Jun Water Bill -5 Expense
      11 22-Jun Food Shop -28.43 Expense
      12 23-Jun Salary 1234 Income
      Excel 2010

      Worksheet Formulas
      Cell Formula
      D2 =IF(SIGN(C2)=1,"Income","Expense")
    • 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.

  3. #3
    Board Regular
    Join Date
    Jul 2012
    Location
    Livorno, Italy
    Posts
    1,950
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    can you attach a link to your file ?

  4. #4
    New Member
    Join Date
    Aug 2012
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by patel45 View Post
    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.
    Last edited by Tantalus; Aug 27th, 2012 at 03:48 PM.

  5. #5
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Tantalus View Post
    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?

  6. #6
    New Member
    Join Date
    Aug 2012
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by circledchicken View Post
    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...

  7. #7
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Tantalus View Post
    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

  8. #8
    Board Regular
    Join Date
    Jul 2012
    Location
    Livorno, Italy
    Posts
    1,950
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,585
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

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

    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

    Tables

     ABCDEFGHIJKLMN
    1DateItemAmount  May  June  July  
    202-May-12Item 1-300May1 ItemIncomeExpenseItemIncomeExpenseItemIncomeExpense
    304-May-12Item 225May21Item 1 300Item 2301 Item 31.86 
    410-May-12Item 380May32Item 225 Item 325 Item 4 23.28
    515-May-12Item 4-2May43Item 380 Item 7 200Item 516 
    621-May-12Item 5-352May54Item 4 2Item 8 50   
    728-May-12Item 633May65Item 5 352Item 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              

    Spreadsheet Formulas
    CellFormula
    D2=TEXT(A2,"mmmm")&SUMPRODUCT(--(MONTH(A$2:A2)=MONTH(A2)))
    F3=IF($E3>COUNTIF($D$2:$D$1000,F$1&"*"),"",INDEX($B$2:$B$1000,MATCH(F$1&$E3,$D$2:$D$1000,0)))
    G3=IF(F3="","",IF(INDEX($C$2:$C$1000,MATCH(F$1&$E3,$D$2:$D$1000,0))>0,INDEX($C$2:$C$1000,MATCH(F$1&$E3,$D$2:$D$1000,0)),""))
    H3=IF(F3="","",IF(INDEX($C$2:$C$1000,MATCH(F$1&$E3,$D$2:$D$1000,0))>0,"",-INDEX($C$2:$C$1000,MATCH(F$1&$E3,$D$2:$D$1000,0))))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  10. #10
    New Member
    Join Date
    Aug 2012
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com