Separate data set into small tables without using pivots

[dschnack]

Hi, I am looking for some help in taking a large data set on one tab and creating multiple small data tables on another tab based on the owner of the account. I've tried vlookups, index/match, sumifs, ect. but keep ending up with blanks or duplicates.


Any help would be appreciated...sample data file below:

https://www.dropbox.com/s/kgxomwm33r5yees/example%20data.xlsx

 

[Jordan]

Is there a reason why you prefer to not use PivotTables?


I would think the easiest way to split up this data is to apply an AutoFilter to your dataset and then sort by account number. This will group every account of the same number together. Then copy/paste each grouping into your next tab. You can also filter by account to find which IDs are blank so you can update them accordingly.


If your list of accounts and data have some semblance of order but have too many different versions of the same data (like "Account1","Account 1", "ACC1") to the grouping useful, this article may be of some value to you:

http://optionexplicitvba.blogspot.com/2012/03/two-methods-to-help-you-number-your.html

 

[Jai9]

Hi,


Try this in A3 and copy down.


=IFERROR(INDEX(Data!$B$2:$B$17,SMALL(IF($A$1=Data!$A$2:$A$17,ROW(Data!$A$2:$A$17)-1),ROW($A1)),0),"")


This is an array formula.To be confirmed with CTRL + SHIFT + ENTER.


I have made one change in your sheet. In Cell A1 on Buyers tab, I put 1 instead of Buyer 1.


For CPID, Spend change column range B2:B17 to C2:C17 etc.


Jai

 

[dschnack]

Thanks for that article, I'll try a few more things but I don't think that its exactly what we need. The problem sounds simple - from a large data set, pull small tables from a specific metric for each buyer - but I can't get the logic into excel.


I'd prefer not to use pivots because of a set sequence we have in place on the small tables. We maintain a specific layout so that each buyer has the same number and same types of accounts, this is just an example data set but it follows the structure that we are trying to put in place: update below


https://www.dropbox.com/sh/lwf38bq49f0c2p5/MxDoCeLl7G

 

[dschnack]

Hey Jai,


I tried that formula and it worked correctly for the first account for each buyer, but I got repeats after dragging it down. I replaced the "buyer 1" with 1 and ensured that references were still correct while dragging. Were you able to successfully pull it down and populate the list?

 

[Jai9]

Hi,


You might have entered this as a normal formula i.e. just pressed ENTER. This is an array formula.


After pasting the formula in the cell, do not just press enter, press CTRL + SHIFT + ENTER.


You will see braces { } around the formula.


After this, copy down the formula.


Let me know if this works.


Jai