Unique value from Excel Sheets 1

[JVZ]

Hi all, I help some help again...

This project requires me to create some monthly reports using Excel. I have three sheets something like:

Consolidated
WestData
EastData

Within those sheets I have a column called states. I wanted to know if there is a way to get all the unique states from the WestData, and EastData sheets (there in column A), and populated them on the Consolidated sheet in column A. Can this be done in Excel using a function with in Excel or VBA..? I currently doing this w/SQL...However I rather save a trip to the database and have in done in Excel.

Thanks for your help in advance...

 

[RobBroekhuis]

Using VBA code you can do just about anything. It's easier if the tables can be sorted on the states column. How are you consolidating? i.e., if there are more than one record of the same state, are you creating totals, etc.?
Rob

 

[Zathras]

It would be fairly easy to write a macro that has the following interface:

Sub CombineLists(ListA as Range, ListB as Range, CombinedList as Range)

But it would be helpful to know a little more about the lists being combined. E.g., within each list, are the values unique? sorted? (If so, the code would be quite a bit simpler.)

Can you provide more details as to what exactly is needed?

 

[SkipVought]

Hi,

Use a Pivot Table to summarize your states. No VBA at all!

VOLA! Skip,
Skip@theofficeexperts.com

 

[JVZ]

Sorry about that,

Well on the data sheets, there will only be one instance of that state, however the below example could happen, also the sheet are sorted by MOUs...

EastData:

State MOUs MOUs/Total MOUs Billed Amount MSGs
NY 163,341,092 44.60% 1,649,324.18 26,570,811
MA 61,988,622 16.93% 361,809.64 10,149,086
NJ 55,879,411 15.26% 439,971.81 9,843,222
TX 55,696,940 15.21% 493,541.10 8,792,235
...
...
Totals 366,233,541 100.00% 3,270,357.34 60,347,533


WestData:

State MOUs MOUs/Total MOUs Billed Amount MSGs
FL 932,286 75.01% 10,566.49 182,614
AZ 310,610 24.99% 4,373.40 68,546
TX 5 0.00% 0.03 1
...
...
Totals 1,242,901 100.00% 14,939.92 251,162

How are you consolidating?
Well what I would like to do (but haven't figured that part out yet)is have consolidated sheet show only the unique State values from both sheets and post there totals, so something like this...

Consolidated:

State MOUs MOUs/Total MOUs Billed Amount MSGs
NY 163,341,092 48.30% 1,649,324.18 26,570,811
MA 61,988,622 18.33% 361,809.64 10,149,086
NJ 55,879,411 16.53% 439,971.81 9,843,222
TX 55,696,945 16.47% 55,696,940 55,696,941
FL 932,286 0.28% 10,566.49 182,614
AZ 310,610 0.09% 4,373.40 68,546
Totals 338,148,971 100.00% 58,162,986 102,511,221


I considered using a Pivot table...however I'm trying to automate this process...because at the end of the month these sheet need to be created...so the real workbook looks like...

Jan_EastData
Jan_WestData
Jan_Consolidated
Feb_EastData
Feb_WestData
Feb_Consolidated...and so on

The problem with Pivot tables is that I would have to update the range every month and with 196 reports to produce in 5 day...and other project on the horizon I really want to try to automatic this...somehow .. if possible within Excel.

Does this help...? Again thanks you your replies...I'm kind of new to "really working w/Excel" and VBA...

 

[Zathras]

It can be done with VBA, but from your description it appears that the simplest way by far is to go to the database. That handles you consolidation and sorting all in one pass, and will be easier to modify when the inevitable change requests arrive. (More columns, fancier ratios, etc.)

 

[SkipVought]

Hi,

If you call the following procedure on the Worksheet_Change event of the sheet where your data is stored (I have the data range called dbStates, then in your PivotTable data reference, use =dbStates and EVERY TIME, your data will refer to the correct range of data

Sub ResizeTables()
    With ActiveSheet.Cells(1, 1).CurrentRegion
        ActiveWorkbook.Names.Add _
            Name:="dbStates", _
            RefersTo:="=" & ActiveSheet.Name & "!" & .Address
        .CreateNames _
            Top:=True, _
            Left:=False, _
            Bottom:=False, _
            Right:=False
    End With
End Sub

hope this helps Skip,
Skip@theofficeexperts.com

 

[JVZ]

Yeah thats what I though...Thanks again for all your input, and time!! Just though I could try to save some trips to DB (trying to increase performance )

 

[SkipVought]

It is not good database design to have similar data in different tables. All your data ought to be on a single sheet from which you can more easily derive the analysis that you might need. Skip,
Skip@theofficeexperts.com

 

[DaleWatson123321]

JVZ,

I believe I can help - by using a seldom-used but POWERFUL component of Excel... it's "database functions". This includes both mathematical functions (=DSUM, =DCOUNTA, etc), but also "database manipulation" functions. This includes the ability to extract data, selectively, from sheet to sheet. Both these two types of functions are based on record selection by specifying "criteria", and one can specify whatever criteria is required to extract the required data from a database within Excel, combine that data with other data, etc.

This "database" component of Excel is NOT well documented by Microsoft, but is nontheless VERY POWERFUL and useful.

If you'd like to achieve your objective, then the recommendation I would make is that you email me your file. Replace any sensitive data with fictitious data that still reflects the type of data you're working with. I'll then make the necessary changes and return the file. Working with the actual file will save considerable time at both ends.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[RobBroekhuis]

I'm certainly not going to disagree with anyone suggesting a more appropriate (i.e., database or pivot table) approach to this general problem. At the same time, it seems like these tables will, per definition, have 50 or fewer items each. With that small amount of data, even a brute-force VBA approach would be lightning-fast, and certainly would provide all the flexibility needed. I would approach it as follows:

Copy the east and west data to the consolidated sheet, one below the other.
Sort the resulting table by state.
Loop through the table, consolidating duplicate state entries.
Re-sort the table, now by MOUs.
For a single table, this will take less than a second in Excel VBA. Add some userforms and subs to select month, pretty up the resulting table, and you got a solution.
Rob