Aurgh, need help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

spirosu - 29 Dec 2005 21:20 GMT

I have 3 spreadsheets all which contain the similar data in this one
single column. The three spreadsheets are being used by 3 different
users. Each one is filling out different rows to that one column.
What I need now is a formula to combine the information in that one
column from their spreadsheets into a master column. I just want to
keep the unique records though.

Hope someone can help.

regards,
Spiro

Signature

spirosu

Reply to this Message

Dennis Saunders - 29 Dec 2005 23:25 GMT

Perhaps a little macro which does
select 1st sheet A1 to end down copy
sheet (your master) select A1 paste
select sheet 2nd A1to end down copy
sheet (your master) select A1 end X 1 down select...offset 1(down) paste
etc

Reply to this Message

Pete_UK - 29 Dec 2005 23:56 GMT

Once you have combined the three sheets into one column (eg column A),
sort the data in that column. Then add the following formula in B2:

=IF(A2=A1,"duplicate","unique")

Copy this down column B for as many entries as you have in column A.
Apply autofilters, and filter column B for "duplicate". Highlight the
rows displayed, and select Edit | Delete Row. Remove the filters and
delete column B and you will have your unique values in column A.

You can do this directly on column A by using Data | Filter | Advanced
filter, checking unique entries only and other parameters as
appropriate (i.e. filter in place or put unique records at some other
location - you specify where).

Pete

Reply to this Message

Max - 30 Dec 2005 02:44 GMT

Here's another approach using non-array formulas to "auto-stack" inputs
from the 3 input sheets and "autofilter" uniques only into one
"consolidated" col

Sample construct available at:
http://www.savefile.com/files/6525629
AutoStacknFilterUniques_From3Sheets_spirosu_gen.xls

Assume data input in 3 input sheets: U1, U2, U3
is made within A1:A10 (10 rows each)

In a new sheet: Consol
Put in (normal ENTER all formulas):

A1:
=IF(ISERROR(SMALL(C:C,ROW(A1))),"",INDEX(B:B,MATCH(SMALL(C:C,ROW(A1)),C:C,0)
))

B1:
=IF(ISERROR(SMALL(Consol!D:D,ROW(B1))),IF(ISERROR(SMALL(Consol!E:E,ROW(B1)-M
AX(D:D))),IF(ISERROR(SMALL(Consol!F:F,ROW(B1)-MAX(E:E))),"",INDEX('U3'!A:A,M
ATCH(SMALL(Consol!F:F,ROW(B1)-MAX(E:E)),Consol!F:F,0))),INDEX('U2'!A:A,MATCH
(SMALL(Consol!E:E,ROW(B1)-MAX(D:D)),Consol!E:E,0))),INDEX('U1'!A:A,MATCH(SMA
LL(Consol!D:D,ROW(B1)),Consol!D:D,0)))

C1:
=IF(B1="","",IF(COUNTIF($B$1:B1,B1)>1,"",ROW()))

D1:
=IF('U1'!A1="","",ROW())
E1:
=IF('U2'!A1="","",MAX(D:D)+ROW())
F1:
=IF('U3'!A1="","",MAX(E:E)+ROW())

Select D1:F1, copy down to F10
(cover the max extent of data in each of the input sheets U1 to U3)

Select A1:C1, copy down to C30
(cover the aggregate of the max extents in the input sheets,
i.e. 10 rows per input sheet x 3 input sheets = 30 rows)

Col A will return the auto-stacked, unique inputs from the 3 input sheets
(Col B returns all inputs irrespective, auto-stacked)

Adapt to suit
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

> I have 3 spreadsheets all which contain the similar data in this one
> single column. The three spreadsheets are being used by 3 different
[quoted text clipped - 13 lines]
> spirosu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7442
> View this thread: http://www.excelforum.com/showthread.php?threadid=496797

Reply to this Message

Ron Coderre - 30 Dec 2005 03:12 GMT

You might be able to use MS Query to consolidate the ranges from your
multiple wkbks/wkshts. This also works for consolidating data from the
active workbook (Just save it first so Excel can find it):

This example uses 3 named ranges in 3 different workbooks.
(Each range contains 2 columns: DeptID, UserInput)

Assumptions:
The data in each wkbk is structured like a table:
--->Col headings (DeptID, UserInput)
--->Columns must be in the same order

The data in each wkbk must be named ranges.
--->I used rng1111Data for dept 1111's data, rng2222Data for dept 2222,
rng3333Data for dept 3333
--->You may use the same range name in different wkbks.

(Note: MS Query may display warnings about it's ability to show the
query ...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)Data>Import External Data>New Database Query

>Databases: Excel Files

Browse to one of The files, pick The data range to import.
--->Accept defaults until the next step.

At The last screen select The View data/Edit The Query option.

Click the [SQL] button

Replace the displayed SQL code with an adapted version of this:

SELECT *
FROM `C:\Dept1111`.rng1111Data
union all
SELECT *
FROM `C:\Dept2222`.rng2222Data
union all
SELECT *
FROM `C:\Dept3333`.rng3333Data

Return the data to Excel.

Once that is done....to get the latest data just click in the data
range then Data>Refresh Data.

You can edit the query at any time to add/remove data sources and/or
fields.
Note: The apostrophes in the SQL are on the same key as the tilde (~).

I hope that helps.

Regards,
Ron

Signature

Ron Coderre

Reply to this Message

Max - 30 Dec 2005 05:48 GMT

Just to clarify that the inputs will be stacked sequentially
in the order of the sheets as per the sheetnames, i.e.:

Inputs in U1,
Inputs in U2,
Inputs in U3
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Reply to this Message