Re: Excel data consolidation question

drodysill@xxxxxxxxx wrote...
I had a question -- I have a series of lists of users based on a userid
text string and their name, some of which are duplicates. What i've
done is import the lists into excel and then do a sort by userid.
Additionally, I made a note of which database they're each in, using a
different column for each of the four databases. I first copied all of
the entries from "users1" and then copied "users1" into every row as
column C. Next, I copied the "users2" list and for those, put "user2"
in column D...and so on up to users4.

Now, what I want to do is consolidate the entries, making sure there is
only ONE entry PER userid, but say if a user is in "user2" and "user3"
and thus has those entries in column D and E respectively, I want to
note both. Basically, I just want one row per userid and for each
userid, that row should include all "user(x)" column entries that they
exist in. Any ideas? I could also use access if that would be easier to
do this.
....

So each list contains just user ID (UID) and name (UNAME) fields? And
the consolidated table's col C through F would also contain the UID?

You need to start with a master list of all UIDs, which means it'd be
easier to add a 3rd column to each table for the list ID (LID), so
user1 would have LID 1, user2 LID 2, etc. Then append the augmented
lists into a single long list with 3 columns. I'll call it SLL. Sort it
in ascending order first by UID then LID.

At that point you could use Advanced Filters to extract only one
instance of each UID and UNAME in SLL. Then you could add formulas to
the columns to the right of the distinct UID and UNAME to indicate the
UIDs' presence in each of the original lists. If the topmost UID and
UNAME were in X2:Y2, try the formulas

AD2:
=MATCH(X2,INDEX(SLL,0,1),0)

AE2:
=COUNTIF(INDEX(SLL,AD2,1):INDEX(SLL,AD2+3,1),X2)-1

Z2:
=IF(COUNTIF(INDEX(SLL,AD2,3):INDEX(SLL,AD2+AE2),COLUMNS($Z2:Z2)),X2,"")

Fill Z2 right into AA2:AC2, then select Z2:AE2 and either double click
on the fill handle or fill down into the same rows as the UIDs and
UNAMEs in cols X and Y.

If you could live with 1s or 0s indicating whether or not a particular
UID appears in a given table, you could use SLL to generate a pivot
table with UID and UNAME as the row variables and LID as the column
variable with operation COUNT.

.

Relevant Pages

  • Re: Multiple predecessors
    ... It appears that it sorts the predecessor list based on the unique id of the predecessor tasks. ... If you sort or move tasks in the list, ... CID UID Name ... The plain Predecessor column lists the same tasks in the same order as their Unique IDs but displays their current ID number no matter what the ID number is. ...
    (microsoft.public.project)
  • Re: List of "user-level" root commands?
    ... jl> since you've only mentioned NFS that you're concerned about users ... jl> changing their UID and snooping around. ... jl> then add to it as your developers request new commands. ... with a list (or lists) of commands that others have already found useful ...
    (RedHat)
  • Re: Multiple predecessors
    ... CID UID Name ... If you add a column to the Gantt chart table for the Predecessor Unique ID ... column lists the same tasks in the same order as their Unique IDs but ...
    (microsoft.public.project)
  • Re: Excel data consolidation question
    ... Databases are a much better long term solution. ... done is import the lists into excel and then do a sort by userid. ... So each list contains just user ID (UID) and name (UNAME) fields? ...
    (microsoft.public.excel)
  • Re: Excel data consolidation question
    ... Yes, each list contains the UID and UNAME fields, in addition to a few ... lists into a single long list with 3 columns. ... I'll call it SLL. ...
    (microsoft.public.excel)