Excel Transcation List to Summary

MrT82

Board Regular
Joined
Dec 12, 2005
Messages
84
Hi All,

I was wondering if anyone could tell me how to get round this problem, hopefully my description of what i am trying to do shall make sense.

I have a sheet listing a number of transactions on various 'cost centres', i then have a further sheet for each cost centre that needs to take the information from the transaction sheet and just pull out the relevant ones for that cost centre. At the moment i go through manually and do the old copy and paste scenario. Does anyone know how i can get excel to do this electronically for me at a click of a button?

This is an example of what i have on my transaction sheet:

CC Value Description
5AYA 200.00 Payment to Company A
5AYA 100.00 Payment to Compan C
5AYB 700.00 Payment to Company A
5AYB 990.00 Payment to Compan C
5AYC 125.00 Payment to Company A

Then i have a further sheet for example for cc 5AYA

Transactions for 5AYA

CC Value Description
5AYA 200.00 Payment to Company A
5AYA 100.00 Payment to Compan C


How can i get Excel to automatically pull this information off the first sheet for me? I have had a look through all the formulas and functions, however i can't find anything, or i'm overlooking it...

Thanks,

Paul
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

KenCriss

Active Member
Joined
Jun 6, 2005
Messages
326
I use this macro a lot. Select the cell that you want to look for duplicates and the macro will copy all occurances to a separate sheet. (Copies entire row). Hope it helps.
Code:
Option Explicit
Sub Cond_Copy()
    Dim returnsheet As String
    Dim Wksht As Worksheet
    Dim newsheet As Worksheet
    Dim arearange As Range
    Dim myrange As Range
    Dim x As Variant
    Dim cell As Variant
    Dim i As Long
    Dim Newrange As Range
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    returnsheet = ActiveSheet.Name
    For Each Wksht In Worksheets
        If Wksht.Name = "Cond_copy" Then
         Sheets(Array("Cond_copy")).Delete
        End If
    Next Wksht
    ' Make a working sheet
    Set newsheet = Sheets.Add(Type:=xlWorksheet)
    newsheet.Name = "Cond_copy"
    Sheets(returnsheet).Select

'   Define area that matches selected cell value
'    MsgBox ActiveCell.Address
    Set arearange = ActiveCell.CurrentRegion
    Set myrange = Intersect(ActiveCell.EntireColumn, arearange)
    x = ActiveCell.Value
    For Each cell In myrange
        If cell.Value = x Then
            If i = 0 Then
                Set Newrange = cell.EntireRow
            Else
                Set Newrange = Union(Newrange, cell.EntireRow)
            End If
            i = i + 1
        End If
    Next
    Set Newrange = Intersect(Newrange, arearange)
    
'   Copy & Paste
    Worksheets("Cond_copy").Select
    Cells.ClearContents
    Range("A2").Select
    
    Newrange.Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
    
    Sheets(returnsheet).Select
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    MsgBox ("See Cond_copy sheet")
        
End Sub
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Using Excel,

Try this for each Sheet:

Formula in B1:
=IF(ROW($A2)-ROW($A$1)>COUNTIF(Sheet1!$A$1:$A$500,$A$1),"",INDEX(Sheet1!B$1:B$500,SMALL(IF(Sheet1!$A$1:$A$500=$A$1,ROW($B$1:$B$500)),ROW($A2)-ROW($A$1))))

You will need to adjust the range to suit your sheet 1 range and you may have to change the name Sheet1 to suit your Summary Sheet name.

Evaluated with CTRL+SHIFT+ENTER, then copied to column C and then copied down as far as you wish.
 

MrT82

Board Regular
Joined
Dec 12, 2005
Messages
84
Hi NBVC,

Thanks for the formula it's greatly appreciated. I got it to work in my first row, however when i copied the formula down it didn't work. Any ideas?

Thanks,

Paul
 

MrT82

Board Regular
Joined
Dec 12, 2005
Messages
84
Hi KenCriss,

I also tried your method, thank you and it worked fine.

Do you know how i can automate it so it will go down each different cell in order that i don't have to click on the cell i want it to look for each time?

Thanks,

Paul
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
MrT82 said:
Hi NBVC,

Thanks for the formula it's greatly appreciated. I got it to work in my first row, however when i copied the formula down it didn't work. Any ideas?

Thanks,

Paul

Did you press CTRL+SHIFT+ENTER instead of just ENTER after you typed or pasted the formula in?

You should see curly parenthesis { } surrounding the formula.

Then copy it over and down.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,504
Messages
5,832,106
Members
430,111
Latest member
Francis Xavier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top