LinkBack URL
About LinkBacks
Hello. In sheets 1-10, I want to have all unique values returned into a master tab, without having to go into each tab manually.
Hello. In sheets 1-10, I want to have all unique values returned into a master tab, without having to go into each tab manually.
I would loop through sheets, copy all values in col B to a new sheet, sort and remove dups, then move that final column to your destination page. you'll need a pair of worksheets called "temp" and "dest" for the below to execute
it's a quick and dirty, a lot of ways to clean it up, but it should work
Please Login or Register to view this content.
Is this thread related to this one you posted earlier today?
Last edited by Trebor76; 09-22-2015 at 10:25 PM.
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
Cool. I'm working through this right now. How would you change it to select the last used row AND the first used row in each sheet? Right now it's selecting and copying a lot of blank rows, not that it matters all that much, but still. Thanks.
WHat are you doing in this part of the code? Why do you select range A1:A38?
Please Login or Register to view this content.
Also, the only problem with this is that it messes up the order of everything. It almost creates more work than it saves because now I have to resort that sheet to match the format of the other sheets. Think about the work of recreating a balance sheet.
Ah, sorry. In my example I had simply recorded an action, forgot to go back and clean it up to be generic. my test data was populated from A1:to A38 apparently. Change that line for just sorting on col A
hmmm.. well, i've done this before for that problem:
then when you do your actual sheet manipulations doPlease Login or Register to view this content.
Or insert a conditional inside your codePlease Login or Register to view this content.
Please Login or Register to view this content.
For your application I'd probably do the last one there since you are only needing to access the data once per sheet. If you have reason to keep referencing the data for some reason in your code, setting the defined range may be useful.
Last edited by Ouka; 09-24-2015 at 07:27 PM.
Ah, if you want to preserve the initial order format but still remove duplicates, that can be a little harder but doable. Is there a sanitized workbook you can share so I can have an idea of what sort of info you are trying to preserve? There are a couple of ways to do what you are looking for but depends on what your data looks likeOriginally Posted by ammartino44
here's a code snippet that might get you moving in the right direction in case you can't share:
It'll take longer to run but should work.Please Login or Register to view this content.
Consolidating from your other thread: http://www.excelforum.com/excel-prog...ach-sheet.html
Re:
Please Login or Register to view this content.
It most certainly does overwrite the data on Master. And it is copying the same col of data over and over. Go ahead and test yourself.
Your Master tab will only contain data from Sheet3. And it will have duplicated Sheet3, col 1 data into cols 1, 2, and 3 in Master.
Let's move this over to your main thread, you are making too many similar threads.
Last edited by Ouka; 09-23-2015 at 10:04 PM.
@ ouka. Oh yeah. I fixed it.
Sub tree()
Please Login or Register to view this content.
Here is an example. Sheet 3 is the most recent version. RESULT is what I want. Obviously in my real spreadsheet there are a lot more seperations but this is sort of the gist of what I want. I want the unique values but I don't want to lose the order and formatting (the order is more important).
well, couple of problems with preserving the order - it's going to preserve the order in step with the way they are ordered on spreadsheets, not an internal order to the final list, unless you want to do a sort on them. see attached book. I have already inserted the code, click the button on "temp" to fire it.
What this code does:
1. clears the temp sheet
2. step through any number of worksheets n and loops through any values in col B of the current sheet that are not empty
3. checks each of those values against any values on the temp sheet, col A.
4. If value already exists, do nothing
5. If value does not exist, move to end of list on temp sheet.
6. repeat for all sheets
The draw back is that regardless of the internal order of info on any given sheet you are only tacking on values that have not been found previously on temp, not inserting them into an order.
The only way to get it into an order would be to create a unique array of all values on all sheets, ordering them, then dumping all at once into temp sheet.
That's a lot of work. It would be far easier to allow a sort at the end of the existing routine to re-order the values after they have all been moved.
Last edited by Ouka; 09-24-2015 at 06:15 PM.
Yeah, that's not what I was looking for exactly. I feel like an accountant or financial analyst should know shortcuts for doing this. Essentially, I'm asking for a way to compare statements over long periods of time (let's say 10 years). Obviously, the formatting and line items change slightly over the years but the main buckets are the same, so what I want to do is create a master list with all of the unique values (and the same buckets) and then use that list to pull the values from the different statements. Seems like it would be very hard. Thanks for your help Ouka.
That sounds doable but helping with something specific is tough without seeing an example of the actual data formats to see where you need to grab the data from. Is there a way to share a sanitized or mocked-up version of what you are needing to work with?
It's mostly about where items are classified. So items go under certain categories. I attached another simple spreadsheet. Thanks a lot for your help. The master should have all of the unique categories BUT within the same cost classification, so I don't have to reclassify everything. THanks.
Ok, to clarify, the workbook is a single client/customer and you are trying to bin all of their history onto a single page. You have multiple sections per page that are constant, but the entries within those sections differ per sheet.
See attached sheet for groupings. Want to make sure I'm coding towards the right idea before I get started.
Do you have a library of all the section names that will be in use? From the sheets you gave me I see:
Premium Written
Unearned Premium Reserve
Premium Earned
Net Claims Cost
Personnel
If there are others I need to know. Or if section headers always have those leading spaces I could use that to parse section names.
Also, are there columns you want to pull over besides the unique section names? If so what do you want done with that information?
What I mean is: You have the item "Multipay" across each of the three sheets. I condense that into a single item on Master. Am I needing to add together values adjacent to each sheet's "Multipay" together and present the sum of those individual sheet entries on Master? If so, where is that data located in relation to the "Multipay" name cell?
Last edited by Ouka; 09-28-2015 at 10:20 PM.
This will get the unique values from column A of all sheets, except Master, into Column A of Master
Note: I have not looked at any of the attachments. Went strictly by Post #1Please Login or Register to view this content.
Hey Ouka,
It relates to financial statements, but it could relate to any type of historical report that changes slightly for each period. Yes, the categorizations should be constant (although their may be some exceptions, I think that will be very rare).
The sheet that I gave you was such a snippet, to show you how the categories were formatted (this could differ depending on the type of report I want to use the code for). I'm envisioning something that maybe codes the categorizations but I don't know how to make use of those codes ha. Or maybe another approach is better...The leading spaces approach might be a good one.
I do want to pull over additional columns but that part will be easy (just use lookups). Essentially, each tab will be a year or month, and I'm just going to create those columns horizontally across the columns and then just lookup the values (thus the need for all line items to be included) So, no, it won't be a summed value. It will be a value for each tab.
I'm surprised there isn't something out there for this, since it seems like a pretty common task for financial analyst/accountants. Thanks again.
@ Ouka. Yeah, that spreadsheet looks right. The only thing is that items in the classifications won't be so simplistic.
@ jolivanes. That doesn't work.
Strange. Works for me on the attachment of Post #17 when I change "Master" to "MASTER"
Ok. Here's the thing. I need specific information in order to code something. That's how code works. I need to either know exactly what the headers you plan on using are, or I need to know exactly how they stand out from the data within the section. There are a lot of ways this can be done, but I still need to know it if I'm going to put together a solution.
If you have a master list of exact header names, I can use that. I'd create a library of header values and look between them for the data values for each header.
If a header section is always going to have those leading space, I can use that. I would do an Instr lookup for those spaces and then use those to make the above library.
If a header section is always going to have something in Col A but never in Col B, and all *other* entries always have something in Col B, then I can use that. I would use the lack of col B data to define the above library.
If the header has a leading code, I can use that. Anything that has a code would define the library.
If the data has a leading code that relates to a specific header, I can use that. Anything that *didn't* have the code would define the library.
There are tons of possibilities. But anything I write will be useless unless you tell me exactly how the data is differentiated on the sheets.
Code isn't adaptive, it's specific. You can fuzz the logic a little by using a like operator or wildcards, but it's much more limited than a human's pattern recognition ability. Not without a **** ton of coding at least.
So you need to tell me, how *exactly* do you plan on differentiating the header data from the data data. I need to know. Specifically. Which is why a sanitized version of your data would be useful.
And instead of doing lookups post run, you may was well include them at run time. It's no more work to pull over additional columns of data when you are pulling over the first ones. Having to go back later and write lookups is much more painful.
Last edited by Ouka; 10-01-2015 at 10:02 PM.
Ok, I made a simplistic version of my model. Whatever you do on these will also work on my more complete version. Any expense with numbers in front of it is NOT a category, everything else is. The same end goal is to get all of the UNIQUE line items in column A of the master sheet in the order ABOVE the corresponding expense category. Thanks.
@ouka. I just found out that consolidate solves my problem. It creates a master file and keeps all the categoriesI'd still be interested in seeing some code though. Thanks!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks