UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Grouping Like For Like Data, Then Reporting On The Missing Ones., Office 2013    
 
   
twigleaf
post Nov 29 2017, 07:04 AM
Post#1



Posts: 229
Joined: 18-September 03
From: Halifax, United Kingdom


Hi

I have a requirement to combine three reports of peoples names from three departments into one worksheet. Which I have done using a button that basically runs some VBA code to automate a lot of copy and paste and data sorts to end up with a worksheet of names, sorted by surname so they are all in groups of three.

The next step is I need to then look at the list of people, which should all have 3 names to show they are on each of the three department lists. And highlight where one of them is missing and also which of the three they are missing from. So for example Joe Bloggs might be on IT and Marketing lists might be missing from HR etc.

Looked at conditional formatting here, but they just highlight the colours alternately.
https://www.extendoffice.com/documents/exce...d-on-group.html

So I have put in some VBA code to colour code the three entries by sheet, so blue is IT, green is Marketing etc. Now I am stumped on the calculations bit.


What I really need is something that also reports somehow. One thing to highlight them but I need a quick and simple list of where people are missing as this is a monthly report for colleagues, I am merely helping pull it together.

Hope that makes sense, any thoughts or help much appreciated.

--------------------
Cheers

"Some acquire greatness, Others have greatness thrust upon them. You my pointless little friend, have let greatness fly far above your head."
"...but neither of us can understand Access 2007"
Go to the top of the page
 
doctor9
post Nov 29 2017, 10:07 AM
Post#2


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


twigleaf,

If you're fairly good with VBA, this should be do-able, even in Excel. smile.gif

Here's what I'd do. Create a subroutine that:

1. Loops through ALL of the names from all three lists, storing all of the unique names in an array.
2. Sort the array of unique names alphabetically.
3. Sort the worksheet data by two columns; the department and the the people.
4. Loop through the unique names, one at a time.
5. If the current person's name doesn't match the corresponding name in the worksheet, insert a row at that point, and put the current name in that row, including an indicator that the person in the array is missing from this department's list.
6. Each time you reach the end of the list of names, check if there are any remaining rows of data to check. If there are, then there must be another department to check. Go to step 4, continuing down the rows of data.

That's about it. At this point you can sort the data so the missing people are all at the top or bottom of each department list if you like. Then, you can color each department's rows a different color if you like.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
doctor9
post Nov 29 2017, 11:51 AM
Post#3


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


twigleaf,

I threw together a demo that should make my explanation clearer.

Hope this helps,

Dennis
Attached File(s)
Attached File  DeptNameListDemo.zip ( 20.12K )Number of downloads: 2
 

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
twigleaf
post Nov 30 2017, 07:58 AM
Post#4



Posts: 229
Joined: 18-September 03
From: Halifax, United Kingdom


Hi

Thankyou for that, I was about to say my VBA skills are okay, but not great. Haven't done some coding for years so very rusty and having to relearn old skills. Which is mostly fun.

I will take a look at the demo and see if I can get my head round it.

Many thanks.

--------------------
Cheers

"Some acquire greatness, Others have greatness thrust upon them. You my pointless little friend, have let greatness fly far above your head."
"...but neither of us can understand Access 2007"
Go to the top of the page
 
twigleaf
post Nov 30 2017, 10:40 AM
Post#5



Posts: 229
Joined: 18-September 03
From: Halifax, United Kingdom


Hi

Had a look through and I think that is what I am after. Apart from I need to expand to a include a few more names as a maximum (we are nearer 1000).

Might take me a while to understand how the code works though. smile.gif

Will cross over into spreadsheet and see how integrating it goes.

Many thanks again.

--------------------
Cheers

"Some acquire greatness, Others have greatness thrust upon them. You my pointless little friend, have let greatness fly far above your head."
"...but neither of us can understand Access 2007"
Go to the top of the page
 
doctor9
post Nov 30 2017, 10:58 AM
Post#6


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


twigleaf,

> Might take me a while to understand how the code works though.

I did try to add comments to the major sections. But if you'd like more info on the why/how of any part of it, I can explain in more detail. For example, here's a more detailed look at how we create the list of unique names:

CODE
'   Create a list of unique names
'   Loop through every row of data, starting at row 2 (assuming row 1 is headers)
    For intRow = 2 To UsedRange.Rows.Count
'       Loop through every name in the array of unique names
        For x = 1 To intNumPeople
'           Do we already have this name on the list?
            If strName(x) = Cells(intRow, 2) Then
'               We already have this name in the array.  Skip this row.
                Exit For
            End If
        Next x
'       If we looked through every name and didn't find a match...
        If x > intNumPeople Then
'           ...It's a new name.  Increase the count of unique names
            intNumPeople = intNumPeople + 1
'           and add the new name to the array
            strName(intNumPeople) = Cells(intRow, 2)
        End If
    Next intRow


Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
dflak
post Nov 30 2017, 01:15 PM
Post#7


Utter Access VIP
Posts: 6,017
Joined: 22-June 04
From: North Carolina


On Dennis' Post #2 - Steps 1 and 2.

You can also use a pivot table to get a list of unique names in alphabetical order. Then you can overlay the output with named dynamic ranges and continue from there.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
twigleaf
post Dec 4 2017, 10:57 AM
Post#8



Posts: 229
Joined: 18-September 03
From: Halifax, United Kingdom


Hi

Had a quick look through and started getting to grips with the code. smile.gif

Looking at the demo it looks like it isn't reporting correctly for me. So in the Raw data it says Abe is in all 3, Betty is only two etc. However in "Missing" shows Debbie to be the one missing. Doesn't report that Betty is the one missing as well. Am I being dumb or missing something?


If I were to add two columns, surname and department for example. Would the existing code handle them?


I also have an error on used range. All I really did was copy it into my Excel spreadsheet, which is telling me "usedrange" is a none defined variable. Have you declared this elsewhere? Mine is a works PC and default install so possible a library isn't declared or something.

I will try and copy my existing code into the demo and see if that works.


--------------------
Cheers

"Some acquire greatness, Others have greatness thrust upon them. You my pointless little friend, have let greatness fly far above your head."
"...but neither of us can understand Access 2007"
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 05:03 AM