U.S. patent application number 12/570820 was filed with the patent office on 2010-01-21 for flexible multiple spreadsheet data consolidation system.
This patent application is currently assigned to AT&T INTELLECTUAL PROPERTY I, L.P.. Invention is credited to Scott H. Hills, Kurt M. Joseph, Mark A. Nixon, Melissa L. Oldenburg.
Application Number | 20100017698 12/570820 |
Document ID | / |
Family ID | 34115655 |
Filed Date | 2010-01-21 |
United States Patent
Application |
20100017698 |
Kind Code |
A1 |
Hills; Scott H. ; et
al. |
January 21, 2010 |
Flexible Multiple Spreadsheet Data Consolidation System
Abstract
Methods and systems are disclosed that include consolidating
data from multiple spreadsheets. Consolidating data from multiple
spreadsheets includes receiving a selection of a plurality of
spreadsheets via a graphical control panel that includes a
spreadsheet selection area. The spreadsheet selection area includes
a workbook selection area to select workbooks that are not open for
inclusion in the selection of the plurality of spreadsheets.
Consolidating data from multiple spreadsheets further includes
receiving a selection of portions of data from the plurality of
spreadsheets and executing automated consolidation of the portions
of data by retrieving the portions of data and generating a final
report spreadsheet. Consolidating data from multiple spreadsheets
further includes appending the portions of data to the final report
spreadsheet.
Inventors: |
Hills; Scott H.; (Austin,
TX) ; Joseph; Kurt M.; (Austin, TX) ;
Oldenburg; Melissa L.; (Edwardsville, IL) ; Nixon;
Mark A.; (Oklahoma City, OK) |
Correspondence
Address: |
AT & T LEGAL DEPARTMENT - Toler;ATTN: PATENT DOCKETING
ROOM 2A-207, ONE AT & T WAY
BEDMINISTER
NJ
07921
US
|
Assignee: |
AT&T INTELLECTUAL PROPERTY I,
L.P.
Reno
NV
|
Family ID: |
34115655 |
Appl. No.: |
12/570820 |
Filed: |
September 30, 2009 |
Related U.S. Patent Documents
|
|
|
|
|
|
Application
Number |
Filing Date |
Patent Number |
|
|
10604608 |
Aug 4, 2003 |
7617443 |
|
|
12570820 |
|
|
|
|
Current U.S.
Class: |
715/213 |
Current CPC
Class: |
G06F 40/18 20200101 |
Class at
Publication: |
715/213 |
International
Class: |
G06F 17/00 20060101
G06F017/00 |
Claims
1. A method of consolidating data from multiple spreadsheets, the
method comprising: receiving a selection of a plurality of
spreadsheets via a graphical control panel, the graphical control
panel comprising a spreadsheet selection area that includes a
workbook selection area to select workbooks that are not open for
inclusion in the selection of the plurality of spreadsheets;
receiving a selection of portions of data from the plurality of
spreadsheets; and executing automated consolidation of the portions
of data, wherein the automated consolidation comprises: retrieving
the portions of data; generating a final report spreadsheet; and
appending the portions of data to the final report spreadsheet.
2. The method of claim 1, wherein the portions of data include at
least one workbook.
3. The method of claim 1, wherein the portions of data include at
least one worksheet.
4. The method of claim 1, wherein receiving the selection of the
portions of data comprises receiving, via the graphical control
panel, a selection of at least one of rows, columns, cells, tables,
filenames, workbook names, worksheet names, and spreadsheet names
of the plurality of spreadsheets.
5. The method of claim 1, wherein the portions of data comprise
results from searching for specified text within at least one of
the plurality of spreadsheets.
6. The method of claim 1, wherein the automated consolidation
further comprises appending additional data to the final report
spreadsheet, wherein the additional data is not within the
plurality of spreadsheets.
7. The method of claim 6, wherein the additional data includes
workbook names that designate original locations of the portions of
data.
8. The method of claim 6, wherein the additional data includes
spreadsheet names to designate where the portions of data were
originally located.
9. A computer-readable storage medium encoded with computer
executable instructions that when executed by a computer, cause the
computer to: receive a selection of a plurality of spreadsheets via
a graphical control panel, the graphical control panel comprising a
spreadsheet selection area that includes a workbook selection area
to select workbooks that are not open for inclusion in the
selection of the plurality of spreadsheets; receive a selection of
portions of data from the plurality of spreadsheets; generate a
final report spreadsheet; and append the portions of data to the
final report spreadsheet.
10. The computer-readable storage medium of claim 9, wherein the
portions of data include portions of data of the plurality of
spreadsheets identified via a preprogrammed search, wherein the
preprogrammed search specifies a user-selected subset of cells,
rows, and columns of one or more of the plurality of
spreadsheets.
11. The computer-readable storage medium of claim 10, further
comprising computer executable instructions to enable a user to
define the preprogrammed search.
12. A multiple spreadsheet data consolidation system comprising: a
computer-readable storage medium encoded with computer executable
instructions, the computer-readable storage medium including a
controller, wherein the controller is executable by a computer to:
send a graphical control panel display to a display device, the
graphical control panel comprising a spreadsheet selection area to
receive a selection of a plurality of spreadsheets that include
specified portions of data, wherein the spreadsheet selection area
includes a workbook selection area to select workbooks that are not
open for inclusion in the selection of the plurality of
spreadsheets; generate a final report spreadsheet; and append the
desired portions of data to the final report spreadsheet.
13. The system of claim 12, wherein the controller further includes
a custom search module that is executable to implement a
preprogrammed search to identify the specified portions of
data.
14. The system of claim 13, wherein the preprogrammed search
specifies a user-selected subset of cells, rows, and columns of one
or more of the plurality of spreadsheets.
15. The system of claim 12, wherein the controller is further
adapted to append spreadsheet names identifying sources of the
specified portions of data to the final report spreadsheet.
16. The system of claim 12, wherein the spreadsheet selection area
comprises a worksheet subset selector.
17. The system of claim 12, wherein the graphical control panel
further comprises a status indicator.
18. The system of claim 12, wherein the controller is further
executable to: track generation of the final report spreadsheet;
report the tracking during generation of the final report
spreadsheet; and present consolidation events as the consolidation
events are being performed.
19. The system of claim 12, wherein the spreadsheet selection area
comprises a window to receive the selection of the plurality of
spreadsheets.
20. The system of claim 12, wherein the controller is part of a
consolidation tool that is executable within one of a word
processing application, a data entry application, a spreadsheet
application, a graphics application, a presentation application,
and a reporting application.
Description
CLAIM OF PRIORITY
[0001] The present application is a continuation of and claims
priority from U.S. patent application Ser. No. 10/604,608 filed on
Aug. 4, 2003, the contents of which is expressly incorporated
herein by reference in its entirety.
FIELD OF THE DISCLOSURE
[0002] The present disclosure is generally related to a flexible
multiple spreadsheet data consolidation system.
BACKGROUND
[0003] The present disclosure relates generally to computer
application programs and data consolidation techniques, and more
particularly, to an application adaptable system and method of
consolidating data contained within multiple spreadsheets.
[0004] In modern business, various reports are routinely produced
and distributed in the form of electronic spreadsheets, which are
typically created using one of several commonly known spreadsheet
and query software programs. The spreadsheets may be located within
one or more workbooks and may include for example supervisor,
manager, and employee names and site locations, as well as
identification, serial, code, revenue, and sales numbers.
[0005] Often the reports are produced at some regular time
interval, e.g., daily or weekly. When a user needs to analyze data
within multiple reports across a time span greater than, for
example, a week, data from multiple individual reports may be
consolidated into a single electronic format, such as a spreadsheet
having desired data from each individual report.
[0006] Unfortunately, this consolidation is usually done manually,
with the user opening each individual report, selecting desired
data from each report, and copying the desired data into a new or
common spreadsheet. Consolidation in this manner is inefficient,
especially for large or complex data sets and complex or numerously
repeated operations.
BRIEF DESCRIPTION OF THE DRAWINGS
[0007] FIG. 1 is a block diagrammatic view of a multiple
spreadsheet data consolidation system in accordance with an
embodiment of the present disclosure;
[0008] FIG. 2 is a sample workbook having multiple worksheets in
accordance with an embodiment of the present disclosure;
[0009] FIG. 3 is a control panel in accordance with an embodiment
of the present disclosure;
[0010] FIG. 4 is a workbook selection window in accordance with an
embodiment of the present disclosure;
[0011] FIG. 5 is a worksheet selection window in accordance with an
embodiment of the present disclosure;
[0012] FIG. 6 is a custom selection window in accordance with an
embodiment of the present disclosure;
[0013] FIG. 7 is a logic flow diagram illustrating a method of
consolidating data from multiple spreadsheets in accordance with an
embodiment of the present disclosure; and
[0014] FIG. 8 is a sample final report spreadsheet in accordance
with an embodiment of the present disclosure.
DETAILED DESCRIPTION
[0015] In each of the following figures, the same reference
numerals are used to refer to the same components. While the
present disclosure describes a system and method of consolidating
data from within multiple spreadsheets, embodiments may be adapted
for use in various other data consolidation systems and methods
known in the art. Also, embodiments may be applied at customer
sites, within remote terminals, at central offices, at call
centers, or at various other sites, terminals, or centers.
[0016] In the following description, various operating parameters
and components are described for a particular embodiment. These
specific parameters and components are included as examples and are
not meant to be limiting.
[0017] Also, in the following description the term "search" refers
to searching through selected spreadsheets for desired portions of
data to be consolidated. For example, a search may include locating
rows of data within desired spreadsheets that have a particular
manager name for consolidation thereof. Other searches are
described in further detail below.
[0018] The present disclosure provides a system and method of
consolidating data from multiple spreadsheets. The method includes
selecting multiple spreadsheets. Portions of data from files
corresponding to each of the spreadsheets are also selected.
Automated consolidation of the portions of data is executed. The
consolidation includes retrieval of the portions of data,
generation of a final report spreadsheet, and appendage of the
portions of data to the final report spreadsheet.
[0019] One of several advantages of the present disclosure is that
it provides an efficient automated spreadsheet consolidation method
for consolidating desired information from multiple spreadsheets,
thereby saving time and costs involved in performing a
consolidation process.
[0020] Another advantage of the present disclosure is that it
provides flexibility by allowing a user to select desired
spreadsheets and information within each spreadsheet for
consolidation thereof.
[0021] Furthermore, the present disclosure is versatile in that it
may be applied to various applications including spreadsheet and
query based software programs, such as those from Microsoft.RTM.
Corporation and others including Quattro Pro.TM. and Lotus 123.TM..
Sample spreadsheet and query based programs from Microsoft.RTM.
Corporation include Microsoft.RTM. Excel and Microsoft.RTM.
Access.
[0022] Referring to FIG. 1, a block diagrammatic view of a multiple
spreadsheet data consolidation system 10 in accordance with an
embodiment of the present disclosure is shown. The system 10
includes a main controller 12 that is preferably microprocessor
based. The controller 12 controls operation of the system 10 and
operation of a monitor 14, a memory device 16, a local database 18,
and an interface 20. Although, the memory device 16 and the local
database 18 are illustrated as separate components, these
components may be combined into a single memory and may be in the
form of RAM and/or ROM. The memory device 16 may be utilized for
performing immediate tasks whereas the local database 18 may be
used for storing information over a longer period of time. A data
entry device 22 is coupled to the controller 12.
[0023] The system 10 may be in the form of a computer, as shown, or
may be in the form of a main frame, a workstation, or other
operating system known in the art.
[0024] The controller 12 includes a software-based consolidation
program 24 for consolidating data from multiple spreadsheets. The
program 24 includes a control panel generator 26 and a spreadsheet
selection procedure 28 for selecting one or more spreadsheets. The
program 24 may have multiple custom window selection procedures 30,
each of which corresponding to generation of windows for selection
of workbooks, worksheets, tables, cells, rows, columns, text or
other custom selections known in the art. The program may also
allow for data to be pulled from various databases known in the
art. A custom search procedure 32 is used for selecting portions of
each of the selected spreadsheets as determined by a user or by the
controller 12. A final report spreadsheet generating procedure 34
generates one or more final reports and appends selected
spreadsheet portions to the final reports. Various other procedures
may also be included and will become apparent in light of the
following description. The above-stated procedures and concepts are
explained in further detail below. The program may be in the form
of a Visual Basic.TM. program or in some other form known in the
art.
[0025] The controller 12 may be coupled to an Internet 36 or a
network 38 via the interface 20. The controller 12 may access
various memories or databases locally, such as memory 16 and
database 18, or may access memories and databases remotely that are
located at other various locations, via the interface 20.
[0026] The local database 18 may include various file entries and
be formatted into directories including multiple workbooks and
worksheets.
[0027] Referring to FIG. 2, a sample workbook 50 having multiple
worksheets 52 is shown in accordance with an embodiment of the
present disclosure. A workbook, in general, refers to a grouping of
multiple worksheets. A worksheet refers to a spreadsheet within and
associated with a workbook. A worksheet may have any number of
cells and may be arranged in rows and columns, as known in the art,
such as cells 54, rows 56, and columns 58. A worksheet may be
reviewed, selected, retrieved, modified or have some other act
performed thereon or be included therein.
[0028] The cells 54 may have data entries that may include entries
such as supervisor, manager, and employee names and site locations,
as well as identification, serial, code, revenue, and sales
numbers. The entries may also include other information known in
the art that may be contained within a spreadsheet or correspond to
a file containing spreadsheet information.
[0029] The data entry device 22 may be one of a various number of
data entry devices such as a keyboard, a mouse, a touch screen, or
other device used in computer applications. The data entry device
22 may be used to perform various selections as will become more
apparent in review of the following description.
[0030] Referring to FIG. 3, a control panel 60 in accordance with
an embodiment of the present disclosure is shown. The control panel
60 is shown for example and illustration purposes only, various
similar control panels or windows may be used as known in the art.
The control panel 60 may have various arrangements and contain
various selection areas, such as spreadsheet selection areas 62.
The selection areas 62 may include selection devices 63 for
selecting rows, columns, cells, filenames, workbook names,
worksheet names, spreadsheet names, as well as other selection
parameters known in the art. The selection devices may be in the
form of option or command buttons, combo buttons, text boxes, or in
some other form known in the art.
[0031] The control panel 60, as shown, includes a workbook
selection area 64, a worksheet selection area 66, and a custom
selection area 68. The workbook area 64 allows for a user to select
all open workbooks, via an all open workbook selection 70, and to
select workbooks that are stored in the memory 16 or database 18
that are not open, via a file selection 72. The workbook area 64
includes a show open workbook button 74 and a browse button 76 for
showing and listing names of open workbooks and searching
directories and for selecting desired workbooks, respectively.
[0032] The worksheet area 66 allows for a user to select all
worksheets within the selected workbooks, via a worksheet selection
78, or select a subset of specific worksheets within each workbook,
via worksheet subset selectors 80. Although, the worksheet area 66,
as shown, only allows a user to select certain worksheet
identifiers and based on these identifiers search each selected
workbook for worksheets having the selected identifiers, the
worksheet area 66 may be expanded to select and search for a
particular worksheet identifier in selected workbooks only.
[0033] The custom selection area 68 allows a user to select desired
cells, rows, and columns to search within the selected workbooks
and worksheets, via selections 82. The custom search area 68 also
includes selections 84 for custom predetermined and preprogrammed
searches, which may be simply selected without entering desired
cells, rows, or columns to be searched.
[0034] The control panel 60 may include a status indicator 86 and
an event logger 88. The status indicator 86 may state whether the
controller 12 is in an idle state and waiting to perform a
consolidation task, whether a consolidation task is being performed
and indicate name of the task being performed, time left in
performing a current task, conclusion of a task, or other status
indications known in the art. The event logger 88 displays
step-by-step events that are being performed in a ladder or tree
like format, so that a user may track steps that are performed and
observe whether a proper consolidation is being performed. When an
improper consolidation is being performed the user may choose to
abort the current consolidation and perform another consolidation
or simply exit the program 24. An output workbook textbox 90 is
included for entering a name of a final report spreadsheet to be
generated.
[0035] Once the desired selections have been made, the user may
then process the desired consolidation, via a consolidate button
92. The control panel 60 may have other various windows type
features, as known in the art. Although, the selection areas 62 are
shown as being combined into a single control panel, they may each
be separate windows that are separately opened, as is best seen in
FIGS. 4, 5, and 6.
[0036] Although, selections 70, 72, 78, 80, 82, and 84 are shown as
option buttons and although buttons 74, 76, and 92 are shown as
command buttons the stated selections and buttons may be in some
other form known in the art.
[0037] Referring to FIGS. 4, 5, and 6, multiple windows 94
including a sample workbook selection window 96, a sample worksheet
selection window 98, and a sample custom selection window 100 in
accordance with an embodiment of the present disclosure are shown.
The windows 94 may be selected from within a control panel, like
control panel 60, or may be selected elsewhere. The windows 94 may
include a shortcut list, such as list 102, or a folder list (not
shown) and allow for selection of specific files, as shown in FIG.
4. The custom selection window 100, as shown, has multiple folders
104 (only two are shown) for selecting specific cells, rows, and
columns and further includes a folder for selecting or searching
for specific text. A sample text entry box 106 is shown for
selecting text in specified columns. For example, a user may enter
desired text to search for and the system may then select cells,
rows, or columns, having the desired text.
[0038] Additionally, the control panel 60 and the windows 94 may
have buttons for custom search development, in which a user may
store custom searches and reuse them at a later time or date, such
as custom searches A-C shown in custom search area 68 of FIG.
3.
[0039] The control panel 60 as well as windows 94 may be in the
form of a consolidation tool or wizard aid that may be selected
within various word processing, data entry, spreadsheet, graphics,
presentation, reporting, or other software programs known in the
art, including those from Microsoft.RTM. Corporation and others
including Quattro Pro.TM. and Lotus 123.TM..
[0040] Referring to FIG. 7, a logic flow diagram illustrating a
method of consolidating data from multiple spreadsheets in
accordance with an embodiment of the present disclosure is
shown.
[0041] In step 110, one or more spreadsheets are selected as
described above. The spreadsheets may be manually selected by a
user through use of a control panel, such as control panel 60, or
may be selected via the controller 12. One or more workbooks are
selected from workbooks that are currently open and from workbooks
that are stored in the memory 16, in the database 18, or in some
other memory or database. Desired worksheets within the selected
workbooks are also selected.
[0042] In step 112, portions of data from files corresponding to
each of the selected spreadsheets are selected. A user may select
the portions of data, via a control panel, or the custom selections
may be previously stored in the memory 16 or database 18 and be
selected by the controller 12. Rows, columns, cells, filenames,
workbook names, worksheet names, and spreadsheet names of one or
more files corresponding to the selected spreadsheets may be
selected. Also, as stated above, desired text may be entered or
selected for searching and consolidation of data contained within
the selected spreadsheets.
[0043] In step 114, automated consolidation of the selected
portions of data is executed. The consolidation may be executed via
a process, a consolidate button, or the like, or may be executed by
the controller 12 in response to some other task or result known in
the art.
[0044] In step 114A, the controller 12 retrieves the portions of
data from files containing each of the spreadsheets. The controller
12 may retrieve the information from memory 16, from the database
18, or from other storage devices known in the art.
[0045] In step 114B, the controller 12 generates one or more final
report spreadsheets; one sample final report spreadsheet is shown
in FIG. 8. The controller 12 opens a new workbook or worksheet and
appropriately labels row and column headings.
[0046] In step 116, the controller 12 appends the portions of data
from the selected spreadsheets to the final report spreadsheets.
The order as to which the portions of data are appended may be
determined simply by order of selection, which may be specified or
selected in the control panel 60. The order may also be determined
by the controller 12, by a user, or by some other technique known
in the art. The controller 12 may append data to the final report
spreadsheets that are not contained within the selected
spreadsheets. For example, the controller 12 may append a workbook
name or spreadsheet name to designate where the data was originally
located. The portions of data may be appended to cells, rows, or
columns of the final report spreadsheets.
[0047] In step 118, a custom search may be stored for use in future
consolidations. When stored the custom search may be listed in
custom selection area 68.
[0048] Upon finishing execution of the program 24 the final report
spreadsheets may be saved and stored, so that they may be reviewed
and possibly used in future consolidations with other or additional
spreadsheets.
[0049] The above-described steps are meant to be an illustrative
example; the steps may be performed sequentially, simultaneously,
synchronously or in a different order depending upon the
application.
[0050] Referring to FIGS. 2 and 8, which are shown to further
illustrate a sample spreadsheet consolidation in accordance with an
embodiment of the present disclosure. The sample workbook 50 is
shown in FIG. 2, having worksheets 52 that are to be consolidated
into a single spreadsheet. The workbook 50 has a workbook or file
name in the upper left hand corner 120 and each worksheet 52 is
designated by a separate folder having a manager name that is
located in a lower portion 122 of the workbook 50. Each worksheet
52 is similarly arranged.
[0051] The worksheets 52 are consolidated by the controller 12 to
form a final report 124, as best seen in FIG. 8. Data contained
within columns A-I of each worksheet 52 are shifted four columns
over to be in columns E-M of the final report 124. The workbook
name, center names, supervisor names, and the manager names are
appended to Column A-D, respectively. The data originally contained
within columns A-I of each worksheet is appended to columns E-M of
the final report, accordingly, so as to correspond with the
appropriate workbook name, center names, supervisor names, and
manager names.
[0052] The present disclosure provides an efficient automated
spreadsheet consolidation system and method that allows for quick
and easy selection of spreadsheets and consolidation thereof. The
present disclosure allows for custom spreadsheet consolidation and
for custom consolidation techniques to be stored and selected. The
present disclosure therefore, is capable of saving time and costs
involved in performing spreadsheet consolidation for various
different applications. The acts described herein may be directed
by computer software code embodied as computer-readable data on a
computer-readable storage medium. Examples of the computer-readable
storage media include, but are not limited to memory, including RAM
or ROM.
[0053] The above-described apparatus, to one skilled in the art, is
capable of being adapted for various purposes and is not limited to
control systems or other communication systems. The above-described
embodiments may also be varied without deviating from the scope of
the disclosure as contemplated by the following claims.
* * * * *