VBA To Consolidate Activity Records

Nov 29, 2008

I run an activity group wherby members have to complete various events such as swimming, cycling, climbing etc..

I have a worksheet that comprises of columns A - P
Column A = Members Name
Column B = Address Info
Columns C - P equals the activities

As each member completes an activity a marker (the word "DONE") is placed in the relevant column C - P

This results in several records being created for each member with a single entry in one of the columns C - P.

I want to consolidate the data into a single row showing the activities completed for each member

The data is sorted by column A brining all like records together

So if the name in the current row = the name in previous row then move entries in columns C - P up one row then delete the current row.

View 9 Replies


ADVERTISEMENT

Consolidate Duplicate Fields / Records?

Apr 12, 2013

I have spent the last couple hours compiling a large amount of data. I have a list of websites, each have a number of visits and transactions. The list is many thousands strong so i have a huge amount of data to work with but there are a lot of duplicate sites. See below, I have taken a snapshot of some of it:

I've highlighted a duplicate example.

Would it be possible across the entire data set to sum all of the duplicate rows into one. So, for the 3 shown, to be reduced to one row with 228 visits and 2 transactions?

And to do it quickly? As I could go through them manually but I don't have to time to go through 10,000 rows.

View 4 Replies View Related

Consolidate Multiple Spreadsheets (consolidate All The Data)

Oct 17, 2008

I have a workbook that has multiple tabs and need help trying to figure out how to consolidate all the data. I find myself spending hours doing this manually each day.

Here is what I have:

Workbook has tabs labeled....Wk1_Mon, Wk1_Tues, Wk1_Wed, Wk1_Thurs, Wk1_Friday, Wk1_Summary......and repeats all the tabs through Wk5....then I have a Month_Summary tab.

I have 25 users with 25 seperate workbooks each with individual information on each workbook.

I am trying to get a sum of all the data on the Month_Summary tab for each month for each user and as well as a sum of the Month_Summary tab for all 25 users.

The end result I am looking for is to get a Yearly Sum of all the Month_Summary Tabs for all 25 users as well as individual yearly summaries for each users.

I have one main Folder which contains 25 folders (one for each user). Under each user folder there is a seperate Workbook for each month.

View 2 Replies View Related

Get Date Of Most Recent Activity

Apr 11, 2014

I have one spreadsheet with a list of materials by material number. I have another with all sales activity. How can I pull into the first spreadsheet the date of the latest activity from the second spreadsheet?

View 2 Replies View Related

Report The Date Of First Activity

May 15, 2014

have a data set with client id and most dates of activity....

I would like to be able to include within a report the date of first activity and the most recent date of activity....

please see attached sample worksheet...

View 12 Replies View Related

Graph 48 Months Of Activity

Aug 8, 2012

I have a very large spreadsheet with financial information I am trying to graph on a line graph (which I know how to do). I would like to take only the last 48 months of activity and graph that. Is there a formula I can use to select only the last 48 months without reselecting the data everytime?

Example (for simplicity purposes in this example I only want ten months of data

Jan Feb March April May June July Aug Sept Oct Nov Dec
10 12 14 10 9 12 11 12 12 10 9 13

I would like a graph that displays march-december sales figures.

View 2 Replies View Related

Show Activity On Userform?

Mar 6, 2013

I have a user form with several command buttons.

Is there a way to show that excel is calcutaing when one of the command buttons is pressed?

The button starts a series of calculation that take a while to complete.

It would appear to other users of my spreadsheet that excel or the user form is not functioning correctly.

It would be neat if there is a way to show a % complete bar. but a simple graphic or text will also work.

View 2 Replies View Related

Macro For Email Activity

Feb 26, 2009

I sit possible to run a macro that will record email activity from an Outlook inbox and store it in a Excel file. For example, the date received and the subject line for every email that comes into an inbox would be logged.

View 9 Replies View Related

Procedures For Suspicious Activity

Aug 1, 2006

I received a suspicious email from <Excel Help/VBA Help: [forum@ozgrid.com]>. It may be nothing but I am wanting to inquiry about it's origin and content, how would I do this? I of course have received other emails from this site.

View 9 Replies View Related

Automate Monthly Activity Report?

Aug 8, 2014

I have this table that I put together everyday describing the activity of my department. There is the date displayed as "Num-Date", the day of the week and multiple columns of data. Every day I have to add a new row and update the date, and the data, etc. I have a macro that inserts a new row and auto-updates the date and weekday, I also have a web query set up to fetch the data I want and display it right next to the table, but what I want to do is make the table more intuitive and automated.

View 1 Replies View Related

Formula To Count Attendance By Day And Activity

Feb 11, 2013

I am recording attendance to our various activities as follows (screenshot):

Column A show the activity

Columns B onwards show attendance by day of the week (names gathered from list).

Now I've got a simple formula =COUNTA(Sep!B4:AE51) to count the total attendance for the month, modifying the range to cater for each activity, but it would be better if I could keep the range the same and modify the activity.

Also, I would like a formula to show the attendance at each activity by the day of the week (ie to show the total and average attendances for Mondays or Tuesday etc).

View 5 Replies View Related

Graphically Representing Activity Data

Apr 26, 2007

I have Activity data from my production line that details when a job is running "RUN" and when its on stop "SETUP". It looks like this:

Run 0.45 hrs (decimal hours)
Setup 0.1 hrs
Run 1.2 hrs
Setup 0.2 hrs
etc.

I want to be able to graphically present this against a fixed timeline, I have the timeline in a column, (not a row). I have cumulatively summed my times so I have this...

Run 0.45 hrs
Setup 0.55 hrs
Run 1.75 hrs
Setup 1.95 hrs
etc

However I want to be able to establish whether the job was ON RUN or SETUP at each interval of my timeline. For example: if my time line was in 0.01 decimal minute intervals, my chart would look like this:.................

View 3 Replies View Related

Find Duplicate Records Based On Multiple Columns But Keep Records

Aug 10, 2014

I have a range of columns i.e. 23 columns (i.e. B through X). Someone can write records in these columns (starting from B21).

Duplicates are considered the rows with similar data in columns 3 and 11. I know about the removeduplicate method and works really well but i want the duplicates not to be removed. Instead another column shall be checked for date of entry (user will entry date in format dd/mm/yyyy). The newest entry will change the value of the cell in column 4 (islatest column)to TRUE while all other records will be FALSE. This will work with the filtering of data on a pivot table on another worksheet.

View 7 Replies View Related

VBA To Rearrange 11000 Records Into 550 Rows (20 Records Combined Into Single Row)

Apr 25, 2014

Book1 and Book2 are workbooks that I have modified in order to protect private information.

Book1 will have 11,000 records (my example Book1 has only 100). I need to rearrange Book1 such that it looks like Book2. Book2 has 20 complete records from Book1 combined into one single row, and my example Book2 has populated 3 rows only (3 rows x 20 records, making 60 records now appear on 3 rows only).

Macro for getting Book1 to Book2? 11,000 records in Book1 will take a lot of hours to transform into Book2 unless a macro can do the job for me.

Book1.xls
Book2.xls

View 5 Replies View Related

Cumulative Staff Contribution Per Process Activity Cannot Exceed 100%

Oct 2, 2012

I have a spreadsheet whereby process activities can be attributed to the different staff members who complete the activity. Each activity is on a different row and up to three staff can be assigned to each activity, so for example if a level one employee sorts the mail on four out of five days per week I would attribute 80% contribution to the task to the level one employee. If a supervisor sorts the mail on a Friday I would allocated a 20% contribution to that staff group.

My question is how do I prevent someone filling in the spreadsheet from allocating a combined contribution above 100% to any task. This could be for example either putting a number in greater than 100% into one of the allocation columns or a total across up to three different columns above 100%.

The columns where I assign the percentage contribution are E,H and K. I would like to generate an error which says something like 'Cumulative staff contribution per process activity cannot exceed 100%.

View 6 Replies View Related

Show Information While Allowing User To Continue Activity

Jan 27, 2010

I need to show an information box e.g. "Initialising..." that I can show and remove without user interaction. I can't seem to find this using either a userform or msgbox.

View 5 Replies View Related

Incorrect Sumproduct Array: Sum Up The Total Number Of Hours Consumed On A Particular Activity Within A Specified Period

Oct 23, 2009

I am trying to sum up the total number of hours consumed on a particular activity within a specified period.

=SUMPRODUCT(($C38:$C176>=$B7)*($C38:$C176<=$C7)*($B38:$B176=$D5))

Unfortunately, the formula above gives me the total number of activity and NOT the total number of hours within a specified period. I attached the file I'm working on as reference.

View 2 Replies View Related

Userform Database: List Records In A Sheet As Well As Search For Records In A Sheet

May 7, 2006

example of a database user form that will allow me to list records in a sheet as well as search for records in a sheet. I know excel has a built in feature for this but it is menu driven and I need something that is button driven and will allow me to resize the form layout. I was not able to figure out how to do that with the built in form.

View 7 Replies View Related

Consolidate The Sheets

Jun 26, 2009

can i consolidated all my sheet into 1 Consolidated Sheet by one sheet information below another. To make it clear i am showing the result how it should appear and what if the sheet is another workbook how would i write the path.

View 13 Replies View Related

Import, 2 X Pop Up Box & Consolidate

Jan 13, 2010

Then it will ask the user what name it wants to find.... then user inputs the name and the macro searches the 2 newly imported worksheets in column A and B, and if it 'contains' the name then will copy it to a results worksheet (and copy row 2 as a static line from each of the work book to differentiate which sheet it came from) ....

View 14 Replies View Related

Consolidate Y Answers To One Row

May 16, 2013

I have a spread sheet that looks like the following. I need to take all of the Y answers and make one line.

Client
ProductA
ProductB
ProductC
ProductD
ProductE

ABC Inc
N
Y
N
Y
N

[Code] ......

If any of the Product columns had a Y in the row, the line would get the Y. If not, an N.

View 5 Replies View Related

Consolidate The Macros

Jan 7, 2009

I have a spreadsheet that has 25 different sheets on it and all of the sheets have it's own set of private macros. I want to know how do I just take one of the sheets macros and allow it to perform that same function across all the sheets since all the sheets are identical. The function of the macros is to hide/unhide rows for the toggle buttons and to hide/unhide columns for the checkboxes.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Columns("j:o").EntireColumn.Hidden = False
Else
Columns("j:o").EntireColumn.Hidden = True
End If
End Sub
Private Sub CheckBox2_Click()
If CheckBox2.Value = True Then
Columns("p:u").EntireColumn.Hidden = False
Else
Columns("p:u").EntireColumn.Hidden = True
End If
End Sub.............................................

View 9 Replies View Related

Consolidate And Sum Macro

Sep 14, 2006

I have a Spreadsheet that has about 150 rows filled with information. Column A (Part Description) Column B (quantity). Certain parts are repeated multiple times. I am trying to come up with a Macro to assign to a button that will do the following.

1.Grab every part on the sheet that has a Quantity in Column B and insert it in Sheet 4 starting in row 15.

2. Instead of having duplicate parts i want it to sum the quantities of all the matching parts.

Right now i am just using a "Hide 0 Quantity" Macro but it is still a pain.

View 4 Replies View Related

Consolidate Over 60,000 Rows

Aug 5, 2008

I have three worksheets with a large amount of data on them (~60,000 lines). The column titles and layout are exactly the same. I need to either 1) Get all of the data into one worksheet so I can create a standard pivot table, or 2) Analyze all of the information using a multiple consolidation range pivot. When I try to copy the data from one spreadsheet to another I get an error message stating the copy area is a different size or shape from the paste area, even though it is not. When I try to create a pivot table using multiple consolidation ranges the fields returned are Column, Row, etc instead of the data fields I would expect (the column titles - exactly the same in each worksheet).

View 6 Replies View Related

Consolidate Data And Sum Up Cells?

Apr 25, 2013

I have a list of entries, people log how many products they sell a day, at the end of the I need to consolidate that data, to get a sum of how many goods each person sold that week. So on on tab I will have multiple enetries by the same person and on the consolidtaed tab, I want each person's name and Id # to show up on one row, but them I would like a total of how much goods they have sold for that month. I have attached a copy of my file, the consolidated tab shows what I would like to get in the end.

View 2 Replies View Related

Consolidate From Different Tabs Into One Unique Tab

Mar 31, 2014

I have an excel file with a Bill of material for each tab. I'd like to consolidate all the equipments from the differents tab into an unique tab. I enclose a sample: Test BOM.xlsx

I'd like a macro that should be able to detect the name and the quantity of equipment in each tab and copy the data in the 'desired output' tab. I don't know if the same result is possible with a pivot table.

View 6 Replies View Related

How To Consolidate These Files Into One Output

May 26, 2014

how to consolidate file01 to file02 merging them, resulting the expected output sheet. Currently im doing this manually and it really take up a lot of my time this is just a sample scenario. My real world use of this is composed of over 2000+ accounts with different product purchases and billing numbers.

View 14 Replies View Related

How To Consolidate Non-numerical Data

Dec 8, 2008

This should be easy but after searching "consolidate" all that I could find was multiple sheets and complex questions.

I have 5 data columns that I need consolidated into 1 single column. I've tried the consolidate funtion but it appears to only work with formulas and numerical values (unless I cant find the proper command.) I've also tried paste special but it populates the cells following.

I need 5 cells word values pasted into a single cell

View 12 Replies View Related

Consolidate Data From 3 Different Resources?

Mar 19, 2013

I need to pull a data from 3 different SharePoint lists and present it in a new data sheet. So far I know how to create data connections and I am able to see everything in 3 different worksheets but in the same workbook. Also if we have the same Project and IT number we should pull information just from the SharePoint 1 list.

View 1 Replies View Related

Consolidate Various Sheets In One Workbook

Apr 26, 2014

i have a workbook with multiple spreadsheets A1, A2, A3, A4, ...... and I have information in each table, i need a macro to select the range of information and consolidate them into another file, ie the macro would say:

Enter the desired range of sheets:

I would input: from A31 to A34

and create a new new workbook called CONSOLIDATED

example:

[URL]

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved