Spreadsheet w/Logic for IT App Deployments

Hello All,

New to the group and would sincerely appreciate any assistance. Sample spreadsheet attached to provide a visual of what I'm trying to accomplish.

Issue: Need to identify which userid's can be deployed and which userid's can't

Detail: In the attached spreadsheet, column A would list all userid's that the project team wants to deploy an application to. Let say for this example that they want to deploy Adobe Acrobat Pro v9 to replace Adobe Acrobat Pro v7. In my actual spreadsheet, there are 1307 userid's in Column A.

As a result of meetings, we have identified 11 Functional Teams that CANNOT have their users upgraded to Acrobat Pro v9. Using internal LDAP look up tools, I am able to generate a list of userid's for each of those 11 Functional Teams. The numbers vary. Again, in my actual spreadsheet so far, Functional Team #1 has 600 userid's, FT#2 has 2700 userID's, FT#3 has 6700 userid's, etc. I'm not done generating the userid's for each functional team but I know I need assistance once I'm done!! LOL!!

Using Column A, I'd like to see if that same userid exists anywhere in Columns B thru L. If it does, then that specific userid cannot be deployed. I would like that userid to be displayed in column N. And in Column O, I'd like to display the name or names of the functional team that this userid is a member of. It could be multiple teams. This will help me identify users that have multiple functional team memberships. Useful data for future deployments.

Using Column A, if a userid does not exist in Columns B thru L, then that userid can indeed be deployed. I'd like to get that userid displayed in Column M.

If someone can assist, this would be a POWERFUL TOOL for IT folks like myself that do global deployments and global projects. I would definitely like to utilize this as a template that could be fine tuned to the needs of any project where some folks can get deployed initially and some can't for functional reasons. The tough part has always been identifying and segregating those users accordingly.

Thanks and Regards,
---Jeff---

Originally Posted by martindwilson

as given this is messy but avoids unnecessary array formula's, but i suspect your real sheet isnt as straight forward as this!

martindwilson,

Thank you so much for the prompt reply. As is it late evening here in Easton, PA, I wanted to check for replies before going to bed. I will DEFINITELY test this in the morning.

If questions do arise, I will post them accordingly.

Again, thank you very much!!

Regards,
---Jeff---

Originally Posted by martindwilson

bed! its 03:31 here!

martindwilson,

I had a chance to look at the spreadsheet. Thanks again for everything.

What I have found is this:

1. A userid in column A will only report correctly if that corresponding userid is only on the same row. I'll explain further.

Column A is everyone that the project team wants to deploy. That column is indeed sorted alphabetically. Now...columns B-L are different teams who's user base cannot be deployed. Those columns are also sorted alphabetically BUT because each team may have a different amount of userids associated, that corresponding ID may not exist on the same row. With me?

Let's use the first name in the sample spreadsheet I provided. (BobC). His userid is in Row 1 of Column A. He's one person that the project team wants to deploy. Now in columns B-L (Teams that cannot deploy), BobC, depending on how many folks are part of the group, may not appear until row 33. Got me?

What I'm actually looking for (using this example - may not be possible) is for a search of each column (B-L), beginning to end, for a match of that userid. Again, please forgive me if I'm repeating myself but I'd like to make sure we're clear so there isn't alot of back and forth.

I've attached an updated version of your spreadsheet to give you a visual of what I mean.

Lastly, if what I'm asking can be done, it will indeed work for me because our entire employee base (43K users) all have unique userid's. There will never by a duplicate because your userid is used for everything, including email. Even common names, like John Smith...if we had 15 of them worldwide, the naming convention would go SmithJ for the first one, SmitJ1, J2 and so on for the remaining.

Thanks and Regards,
---Jeff---

Originally Posted by martindwilson

bed! its 03:31 here!

Forgot to reply to this!! LOL!!

I know..hehe...but it's going to be a VERY LONG weekend for me. I have Phase 1 of another project going live. As you know, most of my work happens once folks go home!! I can't ask my team to work a weekend without being there with them. I'm 44 years old!! It's takes a lot longer now to recuperate, from the wacky project hours, than it used to!! Best thing for me to do is hibernate like a bear a few days prior, buy a carton or 2 of smokes, and hunker down!! HA!!

---Jeff---

Originally Posted by martindwilson

ok see sheet 2 it looks at sheet 1
i know what you mean i used to work in a big financial house 95% of all my work was after 8pm and at weekends(mind you the overtime was good lol)

Oh Wow!! That looks cool!! I'm a little confused though...

I see you removed Column M from sheet 1. That's the column for Users that can be deployed. Where will those userid's be displayed? Or are you just listing them on sheet 2 as well after the userid's that can't get deployed?

Now for sheet 2, does this mean that I should copy the formula's for Columns M & N all the way down to the total number rows of of userid's I have? Do I do the same for Columns N & O on sheet 1?

Originally Posted by martindwilson

just listing on sheet 2, i assumed there was no point in anything after col L on sheet1 as that is the raw data and sheet 2 is the results so everything goes on there.
you need to list all the users id in sheet 2 column A
it will look for that id in the grid B:L on sheet 1 and do the rest. as for formulas you need to drag down
b2:n2 as far as you need
there are no formulas to use/worry about on sheet one they are superfluous and just a hangover from the first try and can be deleted

AWESOME!! I'll give it a try in about 2 hours...working on a server right now..(you been there too huh?) LOL!!

Will reply after trying.

Thanks again!!

Originally Posted by martindwilson

just listing on sheet 2, i assumed there was no point in anything after col L on sheet1 as that is the raw data and sheet 2 is the results so everything goes on there.
you need to list all the users id in sheet 2 column A
it will look for that id in the grid B:L on sheet 1 and do the rest. as for formulas you need to drag down
b2:n2 as far as you need
there are no formulas to use/worry about on sheet one they are superfluous and just a hangover from the first try and can be deleted

Oh my God man!! This spreadsheet is ABSOLUTELY AMAZING!!! Works like a CHARM!!! I've tested it 5-7 different ways...NO BUGS whatsoever!!

Thank You SOO MUCH SIR!!! May you and family be blessed in all your endeavors.

If you ever need anything that I can repay with, email me personally at [email protected]. We can discuss any needs offline.

Regards,
---Jeff---