+ Reply to Thread
Results 1 to 17 of 17

Counting values in a column that varies in placement- not looking to count from a table

  1. #1
    Registered User
    Join Date
    05-11-2017
    Location
    Princeton, NJ
    MS-Off Ver
    2016
    Posts
    12

    Counting values in a column that varies in placement- not looking to count from a table

    Hi guys,

    I have an export of raw data that I am looking to count the values of. However depending on the account and certain attributes that it has, the column I am referring to for the count function can change (i.e: one account will have the refernce column in DA, but another will have it in DK)

    I have a reference table that I am trying to have as the standard layout for the headers, and I plan to use this formula:

    =IFERROR((INDEX('Account Export'!1:1048576,(Output!$A2+2),MATCH(Output!B$1,'Account Export'!$1:$1,0))),"")

    I have placed a hidden column as a reference with numbers (ascending with respect to the row number) and used this as the $A*+2 value so I can pull the equation down across all the needed rows.

    Ideally, this would then fill the column with the data, and ideally, I would copy and paste as values to reduce the file size and load on the computer. Another tab would then count these (countifs mainly) and tally the totals up. My end goal is to just take a raw file export, copy and paste and the data fields will automatically populate in the reference table I created.

    However pulling that equation down across 500k rows is not ideal and I face a crash everytime.

    Is there a way to bypass the table creation and use a function that can find the header title and begin the count function accordingly? Hope someone can help. Thanks in advance!

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    It would help a lot if you could include a sample workbook. We don;t need all 500K rows, but enough to get a feel for the nature of the problem. I think you can limit the scope of the problem by using named ranges. I'd have to see the layout of the data. I also think MATCH is going to help out a lot.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    05-11-2017
    Location
    Princeton, NJ
    MS-Off Ver
    2016
    Posts
    12

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    hi dflak,

    Thanks for the tip! I've attached a sample sheet that can hopefully shed more light on the matter... three tabs (left to right) are as follows:

    Count Tables: this is the final output table I would like to have the numbers feed into- essentially count functions at the moment, but I'm open to suggestions

    Reference Table: these tables represent the current problem of 500k rows of index-match functions

    Import: where I would like the paste the data (where the columns would likely change depending on the account being audited)


    So the end product would ideally be a single paste function of the data and everything else should count automatically. Thank you again!
    Attached Files Attached Files
    Last edited by jericho0o; 05-12-2017 at 09:14 AM.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    It looks like the Import table is the primary source, the reference table is a subset of the import table and the Count Tables is a summary. Is there a reason you need the Reference Table? In other words, if I could go directly from the Import to Count Tables without the Reference Table at all, would that be acceptable?

  5. #5
    Registered User
    Join Date
    05-11-2017
    Location
    Princeton, NJ
    MS-Off Ver
    2016
    Posts
    12

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    Hi dflak,

    That would be a dream come true. The reference table is there because that was what I believed to be the best possible solution. If there are better alternatives, I am more than open to it!

    Thank you!

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    OK then. Here is my shot at it. First of all I converted the import data to an Excel Table. One of the advantages of an excel table is that it knows how big it is, so any formula or pivot table that references it is always working with exactly the right amount of data. This should take care of about half your problem since you'll only be working with 500K rows instead of the full million.

    Also by eliminating the reference table, we have probably cut the problem in half again.

    Here is some information on Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

    In the attached, I called the table Table_Import. What you can do with this is clear out the rows (not the headers) and copy and paste your real data (again not the headers) into Cell A2. The table will do the rest.

    The formulas you need are in Columns I:K - they should be enough to give you an idea. The rest of the information on the page was for my own diagnosing of the problem. You can remove columns A:G to get a cleaner sheet.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-11-2017
    Location
    Princeton, NJ
    MS-Off Ver
    2016
    Posts
    12

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    Awesome, thanks dflak!

    This helps me make some headway into the problem, but what if the data has a different number of columns? The data pasted will not align- that's the reason why I used the reference table tab- it was a fixed subset of the columns, and the equations in that tab would find the matching header in the imported data (regardless of what column number they were in and pull the data in accordingly) the Count Tables would then pull the accurate data from the Reference Tab, etc, etc...

    Sorry I don't know how to say it properly in Excel jargon, but essentially Count Tables that respond to the varying column locations in the data import tab... is this possible?

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    I had a second thought. Here is an alternative version. Instead of formulas, it uses pivot tables. If you find this significantly faster, then with a couple of formulas we can make it look like your original display. The pivot table would do the "heavy lifting" and the values will be looked up from this series of small "tables."

    Or you can stick with this format. It does have the advantage of being flexible (for example if you add a pink somewhere, it will show up). Copy and paste the data into the import data. Clicking on Data -> Refresh all should refresh all the pivot tables or you can right click on any one of the tables and refresh it. Since they all are built off the same data source, refreshing one table refreshes them all.

    I was typing this as you last post came across.

    Are you telling me that the order of the columns in the import might change? If that's the case, then the table solution may be off the table (pardon the pun), but something can be done with named dynamic ranges. It takes a bit more setup, but accomplishes essentially the same thing. The only thing is, I will need VBA, since I can't depend on any particular column as having contiguous data. I'll await the answer to that question before pressing on.

    Take a look at the attachment here. it will still work whether I have a table solution or a named range solution.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-11-2017
    Location
    Princeton, NJ
    MS-Off Ver
    2016
    Posts
    12

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    Probably would be best if I went down the VBA route dflak- I simplified the sample data sheet, but some of the count functions in the actual sheet would eventually be countifs and pull information from multiple columns- if the count tables all depended on single criteria however, the pivot tables would have been the perfect solution.

    Because of the varying sizes of some of our accounts, some of the rows do go up to a million- but prior to my having started the thread, I put the 500k limit in just to make sure my computer could handle it. Down the road however, I foresee clients being onboarded with larger accounts and more data :/

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    Here is a version using named dynamic ranges. I created one for each of the column headers you wish to track. I named the ranges the same as the column headers.

    Here is information on dynamic ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges.

    There is a parameters sheet that you can hide where I calculate where the header name is found. Also Cell B11 contains the last row that has data - this is the part I needed VB for. There is one idiosyncrasy you need to know. Excel knows the last row as of the last time the workbook was saved. So it very important to save the workbook after importing the data.

    The named ranges are calculated once, not 500K times, so that should speed things up a bit .

    So the process is, copy and paste in all data (including headers). Save the file. Fill in what you are looking for in row one on the Count Tables sheet, copy over the formulas. If you need to add your own COUNTIFS, you're on your own .
    Attached Files Attached Files

  11. #11
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    I suppose you want a version with the actual formulas.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    05-11-2017
    Location
    Princeton, NJ
    MS-Off Ver
    2016
    Posts
    12

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    Hi dflak,

    I'll give these a shot and update you on how it goes. Thanks for your help!


    Keep you posted--

  13. #13
    Registered User
    Join Date
    05-11-2017
    Location
    Princeton, NJ
    MS-Off Ver
    2016
    Posts
    12

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    Hi! Update! Thanks! It seems to work out really well! I'm just going to add those COUNTIFs because someone said I'd be on my own there :P

    Kidding. Thanks so much for the assist with this dflak! Got me way interested in VBA now- excuse me while I start to learn this!


    Best,

    -jericho0o

  14. #14
    Registered User
    Join Date
    05-11-2017
    Location
    Princeton, NJ
    MS-Off Ver
    2016
    Posts
    12

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    Hi Guys, one more issue-

    for some reason, after applying the dynamic ranges, I am writing up countifs functions and the counts are off- is there a reason behind the inaccuracies? I'm manually checking the values and when the proper criteria are applied, it confirms the inaccuracy.

    Thoughts? Would really appreciate it! Thanks!

  15. #15
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    Can you point to an example where it does not appear to work?

  16. #16
    Registered User
    Join Date
    05-11-2017
    Location
    Princeton, NJ
    MS-Off Ver
    2016
    Posts
    12

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    Hi dflak,

    So I took what you wrote up and implemented it into a series of Call Macros to run in sync with a CloseMe and OpenMe macros...

    Before those however, I would make sure that the file would be saved after pasting. The order of operations would be:

    Save File,
    Rerun Dynamic Range Macro (which doesn't seem to be working as it doesn't assign names to the columns-
    Save again
    Close Excel
    Reopen Excel

    Am I missing a step here that's preventing the naming of the ranges? Thanks!

    Best,

    -jericho0o


    **Edit: I can't seem to attach the sample file I had generated (it's 4MB) anyone have tips? :/
    Last edited by jericho0o; 05-16-2017 at 10:59 AM.

  17. #17
    Registered User
    Join Date
    05-11-2017
    Location
    Princeton, NJ
    MS-Off Ver
    2016
    Posts
    12

    Re: Counting values in a column that varies in placement- not looking to count from a tabl

    Hi Guys,

    I've been trying to figure this out, but I haven't been making any progress- I ended up finding a macro to use to help me form the Dynamic Named Ranges:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I got it from contextures (as the first couple lines state) and the problem now is that when I have my countifs, the amounts are off-

    some things to consider:

    There are gaps between data in the columns (I don't know if the script factors that in- not fully VBA literate yet)
    The script is ran after a save sub, then another save sub runs after the script and a close and reopen sub follows that one.


    I'll keep trying to upload a sample file, but I keep getting errors when trying to post.

    Thanks for anyone who can shed some more light on this issue for me.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 08-19-2015, 07:46 PM
  2. Pivot table not counting values (but does count based on labels)
    By andy.k in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-25-2014, 12:56 AM
  3. Replies: 1
    Last Post: 10-31-2013, 08:25 AM
  4. count values in a column and populate table
    By Harrison_VBA in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-25-2013, 07:31 PM
  5. Replies: 12
    Last Post: 02-06-2012, 11:23 AM
  6. Pivot Items Count varies on multiple runs
    By narendramaruti in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-21-2009, 07:38 PM
  7. [SOLVED] Pivot Table custom column placement
    By metrueblood in forum Excel General
    Replies: 1
    Last Post: 08-03-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1