Office 365, Excel for Windows 2010 & Excel for Mac
Posts
29,464
Re: List Unique Suppliers in Summary
Have you thought of using a Pivot Table.
This performs the acts of filtering, summarising and analysing without going anywhere near functions or macros.
If you've never used PTs I suggest you spend 1/2 an hour or so exploring them. You'll be pleasantly surprised at their simplicity once you understand what they're doing and you'll be able to analyse data in ways you've not even thought of yet.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
HI there - yes, normally I use PTs for this task but I have so many of these to do I thought coding it would be better. Currently my spreadsheet imports the relevant data, formats it correctly and summarises it, before saving it using a standardized file name within a few seconds, a PT works, but is more time consuming when doing it hundreds of times.
Office 365, Excel for Windows 2010 & Excel for Mac
Posts
29,464
Re: List Unique Suppliers in Summary
Hi,
That's a slightly different requirement to your original request. I'm interpreting this to mean that you are automating with a looping procedure the import, process & saving of the file rather than doing this once as originally implied.
Notwithstanding that I'm not clear why you think a PT is too time consuming. Refreshing a PT will be much quicker than any macro process to filter and summarise.
Why not create a dynamic range name for the import area of your data and build a PT that uses that range name and just have an additional single line of code in your import macro code to refresh the PT before the Save part of your macro.
That's a slightly different requirement to your original request. I'm interpreting this to mean that you are automating with a looping procedure the import, process & saving of the file rather than doing this once as originally implied.
Notwithstanding that I'm not clear why you think a PT is too time consuming. Refreshing a PT will be much quicker than any macro process to filter and summarise.
Why not create a dynamic range name for the import area of your data and build a PT that uses that range name and just have an additional single line of code in your import macro code to refresh the PT before the Save part of your macro.
Hi Richard, sorry I wasn't clear, I just wanted help with one small aspect of the project so I didn't detail the entirety. Apologies for any confusion, I'll have a look into your suggestion - but I haven't used dynamic range names before... nor have I used PTs from macros before. Hence I was going for the solution where I understand at least 90% of the solution.
First idea is to still use an advanced filter but you already have a code …
So try this demonstration using an array variable as a dictionary (Data row #1 is columns titles) :
PHP Code:
Sub Demo1() Const DATA = "Data", SR = ", " VA = Worksheets(DATA).Cells(1).CurrentRegion ReDim DK$(1 To UBound(VA), 0), DS(1 To UBound(VA), 1 To 2)
For R& = 2 To UBound(VA) V = Application.Match(VA(R, 1), DK, 0) If IsError(V) Then L& = L& + 1: DK(L, 0) = VA(R, 1): DS(L, 1) = VA(R, 2): DS(L, 2) = VA(R, 3) Else DS(V, 1) = DS(V, 1) + VA(R, 2) If InStr(SR & DS(V, 2) & SR, SR & VA(R, 3) & SR) = 0 Then DS(V, 2) = DS(V, 2) & SR & VA(R, 3) End If Next
With Worksheets("Summary") .UsedRange.Clear Worksheets(DATA).[A1:C1].Copy .Cells(1)
With .[A2].Resize(L, 3) .Columns(1).Value = DK Worksheets(DATA).[B2].Copy: .Columns(2).PasteSpecial xlPasteFormats .Columns("B:C").Value = DS .Columns(3).AutoFit End With
Application.CutCopyMode = False: Application.Goto .Cells(5) End With End Sub
Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !
Last edited by Marc L; 05-06-2015 at 04:44 AM.
Reason: optimizing …
LinkBack URL
About LinkBacks
List Unique Suppliers in Summary
Register To Reply
below the post.
Originally Posted by Richard Buttrey
Bookmarks