Extracting date

Copper Contributor

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

$1000                                          $2000

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?

 

Thank you.

13 Replies

@dlucas980 

 

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.

@mathetes 

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.

 
I hope this helps. 

Thanks for your input!

@dlucas980 

 

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:

  1. one which is the account numbers and individual data corresponding to each account (you'll see I reorganized  yours)
  2. the other being--Well, here's where I was asking you what the larger context is--for the time being I'm going to assume it would contain such things as transaction history, deposits, withdrawals, whatever; it could also contain actions/transactions of other types..essentially a history of the accounts in your custody.  I haven't done anything to mock up this, because I wanted to find out what it is you anticipate doing with the individual sheets. FYI, it wold be very easy using the new FILTER function to list any accounts deposits and withdrawals (and other transactions) on the page, extracting them from a comprehensive list of all clients' account histories, putting them in date (or other category) order.......

 

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)

@mathetes 

 

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.

Forgot to add that I do have the laterst version of Excel.

@dlucas980 

 

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.

@dlucas980 

 

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.

 

 

Thank you. I have never used VBA. But, I am willing to try. If nothing else, I will learn something about VBA.

Thank you.

@dlucas980 

 

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.

@mathetes 

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!

@mathetes 

 

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! 

@dlucas980 

 

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.

@mathetes 

I want to create electronic files. So, I think your instructions can get me there. I, too, want to avoid macros. 

 

Thanks for your advice. I am going to give it a go.