Excel Transcation List to Summary

[MrT82]

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

 

[KenCriss]

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.

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]

Using Excel,

Try this for each Sheet:

Book3
	A	B	C	D
1	5AYA	200	Payment to Company A
2		100	Payment to Compan C
3
4
5
5AYA

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]

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]

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]

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.