07-01-2020 10:04 AM
07-01-2020 10:04 AM
I have a worksheet that includes client account information such as Client Name, Client Address, Account Number, Account Balance, Date Opened, Date Closed, etc. This information is collected in columns.
John Doe Jane Smith
123 Main St 321 Elm St
A/C# 001-0001 A/C#001-0002
Jan 1, 2020 Feb 1, 2020
June 20, 2020 Open
Putting all the information in columns is handy for creating pivot tables. But, I would also like to create an individual worksheet for each account so that I can keep the information in separate account files. I would like to automate the creation of this individual sheet.
Does excel have functions that allow for this?
07-01-2020 10:52 AM - edited 07-01-2020 10:54 AM
First off, storing data records in rows is the far more common way, and entirely suited to Pivot Tables. And then you can convert the range of data to an Excel Table and far more easily do other things as well--e.g., take advantage of the newer Dynamic Array functions SORT, UNIQUE, FILTER...
So let me first disabuse you of the notion that having your client account information in columns is suitable. They may work for Pivot Tables, because of the flexibility of Pivot Tables, but they'll end up limiting you more than helping you. Seriously. Check out any texts on data bases and tables in Excel; I doubt you'll find a one that advocates doing the records as columns.
Second, could you say more about what you'll be storing in those individual sheets, one per client, and maybe a word or two about how many clients you're talking about.
It's clearly a database of some kind that you're building. The question is what would be the best database design to accomplish your purposes. Excel is really VERY good at taking a single table and producing a summary report. The Pivot Table, with its cross-tabulated summary of monthly transactions per client (for example) is something you're already familiar with. It's also possible that you'd be better off using a single table for all transactions, keyed (obviously) to each client's account number(s), but still a single table for all of them, and then let Excel do it's trick of separating out the relevant transactions (or whatever) per client......i.e., let Excel do the "heavy lifting" of extracting data based on a few parameters, rather than having to search for each client's separate sheet every time you want to enter something pertinent.
I'm not saying that's a foregone conclusion, but I do want to make sure you've considered the possibility rather than assuming that, because you'll want OUTPUT that is client specific, that means you also need to have the INPUT be on client specific sheets. That premise is not a necessary (or, for that matter, helpful) way to approach your design.
So, if you can forgive the impertinence of my asking you to explain your business goals here rather than just helping you do what you asked, my goal (and I think that of others on this site, many of whom are far more expert with such things as Power Query than I) is to help you come up with the best design, and to do that we need to ask for a description of the bigger context within which you're operating.
07-01-2020 02:16 PM
I attached an example, I am trying to populate information for about 200 accounts (arranged horizontally in the sheet MasterList). I have a list of about 45 items to populate (the example has 5 items). I want to automatically populate individual sheets like the two sheets in the example for all 200 accounts.
07-01-2020 03:38 PM
Bear with me please.
You haven't explained what you're going to do with those individual sheets.
So I'm still going to push to have two basic databases:
My point is that from those two databases (which are all encompassing) you could produce individual sheets on an ad hoc basis.
So I've illustrated that with the example data you've given me. What I'd like you to do with this is play around a bit, seeing how you can add new accounts and have the drop down list on the "Indiv" tab automatically include those new account numbers and therefore easily be populated by the additional data. [I didn't provide room for the second street address, but that's easily handled]
Instead of current account value I created a field called Opening Account Value or something to that effect. Current value is a volatile number, and would be produced in that ad hoc page, based on transaction history (assuming that's what you want).
But please tell me what it is you are going to want those individual pages to show, and then we can talk about whether this kind of design will work.
A caveat: that data validation list that is used to populate the options in the drop down box makes use of the UNIQUE and SORT functions which are only available in the most recent release of Excel, so if they're not working for you, then we'll have to do something else...(or get you that newest Excel)
07-01-2020 03:57 PM
My hope was to create individual forms per account in order to keep them in my client folders. The Master List is related to a regulatory requirement. I will have to discuss an overview of the data with the regulator and be prepared to discuss randomly sampled individual cases.
The Indiv sheet that you created looked pretty good. When I finish designing the report, I expect to have at least 25 data points for each of the 200 accounts. I will play around with it and see how it goes. As mentioned in my original question, I (well, my colleagues) prefer accounts organized by columns and categories in the rows. But, I am comfortable with the transposed view. It looks fine to me.
Thank you very much for you help.
07-01-2020 05:52 PM
OK. I was trying to help with an intro as to how treating your data as a table (aka database) could work. And I was differentiating between that the raw data storage areas (which serves as the input), and what you're now calling a "view," which I would consider "output"....
One of the classical wrong ways (IMHO) to approach designing an Excel workbook is to confuse the two, to think of Excel mostly in terms of what you're expecting things to look like at the output end, planning to enter data that way. This may not be what you're doing, although that "master" layout kind of looks like it. My revamping of it was never intended as a "view"-- solely as a database, from which views (output) would be constructed.
If what you're looking for is 200 (printed?) sheets of basically static info, one for each client folder, then Excel is one way to do it; Word might work too. If, on the other hand, there's a lot of action with those data points, especially if you want to retain history of various events in each account, then I'd suggest you try to differentiate between the data to be stored (and learn about Excel Tables), and then let us come up with ways to extract the data into useful reports, or client snapshots, more dynamically. Doing that is using Excel at its best. Using it primarily as a way to organize data into rows and columns (conceiving it as sort of a semi-automated ledger sheet) just scratches the surface of what Excel can do.
I recognize that may not be what you're doing. But so far, it sounds like it, if only because you have been speaking of "at least 25 data points" but not of a process or series of transactions. It's just (it sounds like) static data, occasionally changing to be sure, but not really tracking transactions for those accounts.... There's nothing wrong with that; it's just that you wouldn't be taking full advantage of what Excel can do.
07-01-2020 07:01 PM
If you are open to a VBA solution, you may try something like this...
Sub SplitDataToSheets() Dim wsData As Worksheet Dim dws As Worksheet Dim shName As String Dim lr As Long Dim lc As Long Dim Rng As Range Dim str As Variant Dim acRng As Range Dim dtRng As Range Application.ScreenUpdating = False Set wsData = Worksheets("Master") lr = wsData.Cells(Rows.Count, 1).End(xlUp).Row lc = wsData.Cells(2, Columns.Count).End(xlToLeft).Column For Each Rng In wsData.Rows(2).SpecialCells(xlCellTypeConstants, 2).Areas str = Split(Rng.Value, Chr(10)) shName = str(0) & " Account" Set acRng = wsData.Range(wsData.Cells(Rng.Row + 1, 1), wsData.Cells(lr, 1)) Set dtRng = wsData.Range(wsData.Cells(Rng.Row + 1, Rng.Column), wsData.Cells(lr, Rng.Column + 1)) On Error Resume Next Set dws = Worksheets(shName) dws.Cells.Clear On Error GoTo 0 If dws Is Nothing Then Set dws = Worksheets.Add(after:=Worksheets(Worksheets.Count)) dws.Name = shName End If dws.Range("B2").Value = Rng.Value acRng.Copy dws.Range("B3") dtRng.Copy dws.Range("C3") dws.Range("A3").Value = "#" dws.UsedRange.RowHeight = 15 With dws.Range("A4:A" & lr) .Formula = "=Row()-3" .Value = .Value End With With dws.Range("B2:D2") .Merge .Font.Bold = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .RowHeight = 50 End With dws.UsedRange.Columns.AutoFit Set dws = Nothing Next Rng wsData.Select Application.ScreenUpdating = True End Sub
In the attached, click the button called "Split Accounts" on Master Sheet.
07-02-2020 06:19 AM
07-02-2020 06:23 AM
A postscript to our exchanges of yesterday. If it's really the case that all you want to do is take records that are organized in columns and take data for single clients and put it on a single sheet for including in a client's folder, so long as the columns are all consistently organized and the output you want is consistently laid out, you could set up a series of XLOOKUP or HLOOKUP functions to do that. (By series, I think it could be a single formula copied to multiple cells, but it's hard to say without seeing the actual source and end result desired). It shouldn't be necessary to write VBA code.
Would it be possible for you to post a complete example, not just the small subset, and do so without any real names and addresses just as you did with the small sample.
It should be noted, too, that if this is really all you are aiming to do--which is legitimate--another solution which might work and give even more flexibility is to use the Excel data as the source for a mail merge document in Word. With that your final layout could have even more "zip"--to the extent that's important. In that case, though, you'd really want the source data to be arrayed in rows, as an Excel Table.
07-02-2020 06:25 AM
Thanks for you comments and clarifications. You are correct that it is mostly static data. Storing useful data and extracting data for useful reports or client snapshots is an accurate description of what I am trying to do.
This is the first time I have sought help on a board. I am blown away by helpful people are!
07-02-2020 06:44 AM
I am pretty comfortable with XLOOKUP and HLOOKUP. But, I do not know if (or how) the individual reports could be automated. My solution might simply be to manually create the individual reports since it only has to be done once per year.
Unfortunately, I cannot give a more detailed example because I am still waiting for input from others, always a slow process!
07-02-2020 08:12 AM
Again, then, if that "Master" sheet is representative, and all the columns are consistently organized, such that row xx always contains data element "Job Title", (etc) you should be able at the basic level to write an XLOOKUP formula that would fill each cell in your individual sheets and use column and row references off to the side as guides within those functions. Or maybe OFFSET would be the function. Key each formula with an absolute reference to the Account Number on the "Master" and let the columns and rows be absolute or relative as needed.
It's not been clear to me whether you were wanting those individual sheets printed or electronic. If the latter, you could just have 200 individual sheets and populate the index (account number) once and be done.
Or write a macro (I did this one time in a comparable situation a couple decades ago) that runs through the account numbers from top to bottom and prints each sheet before going to the next. I generally avoid macros and VBA, preferring the elegance of Excel's many formulas.