inputting unique references

[scouser22]

I have a slight problem

I know how to extract unique references from a list by using the advanced filter, but is there any way it can be done by a lookup or some other function?

I am using a SUMIF to add up all the entries to a specific account, but the list of account numbers changes month on month (some new one are added, and some are cleared so fall off the list).

So I have tables set up to extract the figures from a spreadsheet to sum up the figure, were i have an account number, then 8 spaces below (as the data i am extracting is to the right of these spaces), then the next account number, and so on until all of the accounts are listed.

Is there a way to pick out the unique accounts, and input them with the 8 spaces between, as doing it manually each month would take far too long as there are over 200 accounts

Thanks

 

[Yard]

Can you post an example of your data (what you have and what you want) as I'm confused by your description.

Thanks

 

[scouser22]

is it possible to post full sheet attachments on here?

 

[Yard]

Nope. Excel Jeanie 4 is the tool of choice. You can find a link in the Forum Rules.

If it's not that complicated, just sketch it out in text in your post.

 

[scouser22]

Sheet 1 contains all my data

Sheet 2 looks like:


Branch Number Agency Number Details...............................

Vlookup("From Sheet1") ("Unique Ref Sheet 1") SUM IF based on Agency No.

Is that enough detail?

 

[scouser22]

sorry Branch NUmber, Agency and details are the headings.
and the lookup, unique ref, an sum if are under the headings respectivly

 

[MisterCrash]

Here is a way to do it in VBA. It presumes that the entries on Sheet1 have the account numbers in column A, starting at row 5. It also presumes that the unique account numbers are going to go in column A of Sheet2, starting at row 5, with eight blank rows in between each account number. It doesn't clear out any of the existing data that is already on Sheet2, so you might need to either do that manually or amend the code to do that.

Sub GetUniqueReferences()

'This macro will take a list of unique account numbers in column A of
'Sheet1 and move them column A of Sheet2

'This presumes that account numbers start in row 5 on Sheet1, and that
'they are going to be pasted starting in row 5 of Sheet2, with 8 rows
'of blank spaces between each account number

Dim lngLastRow As Long, lngPasteRow As Long
Dim i As Long
Dim colNoDupes As New Collection

With Sheets("Sheet1")
    'Find the last row in column A
    lngLastRow = .Range("A65535").End(xlUp).Row
    
    'Set up the collection of account numbers
    'Include a key to reject entries that are already included
    On Error Resume Next
    For i = 5 To lngLastRow
        colNoDupes.Add .Range("A" & i).Value, CStr(.Range("A" & i).Value)
    Next i
End With

'Unload the entries on Sheet2
With Sheets("Sheet2")
    For i = 1 To colNoDupes.Count
        'Set the destination row, starting at row 5
        '8 blank rows => the information is filled in every 9th row
        lngPasteRow = i * 9 - 4
        .Range("A" & lngPasteRow).Value = colNoDupes(i)
    Next i
End With

End Sub