I have a game where people are scored on the spreadsheet, but they can join anytime, so everyday, I get a combined list of points. The list consists of people who joined previously, and the new players. How should I combine the points?
[URL] ..........
I need to merge rows with duplicate values in column A (Patient Name being the most important one), with columns B, C, & D usually having different isolated values as well. Columns E, F, G, H, & I are date columns, but the data is always going to be the number 1, meaning a patient was seen once that day (if they were seen two times that day for different reasons, information would be in an unmerged second row [same patient name listed in two separate rows], where columns B & C would be different). Column J is an autosum of columns E through I if that makes a difference. Column K is a notes column. The data that needs to be merged is always added to the bottom of the spreadsheet in order to show that a patient was seen on any given day, with columns B through K almost always being blank. Example:
Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes
Alice Alpha--------AB----------PT-------1---------------------------------------------0-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3----------------------------------------------0
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2---------------------------------------------0
Alice Alpha-----------------------------------------------1
Carl Carlisle--------------------------------------1--------------1---------------1
Ernie Elephant-------------------------------------------1---------------1
This is what I'm hoping it can look like:
Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes
Alice Alpha--------AB----------PT-------1---------------1-----------------------------1-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3-------1--------------1---------------1------3
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2--------------1---------------1-------------2
In this example Carl Carlisle is being seen for two different things, however how would it be written so the macro would know which Carl Carlisle row to merge with? I'm thinking that before running the macro I could manually enter the information into column B so it knows which Carl Carlisle row above to merge with.
Data always starts at row 14 (row 13 is frozen pane header column), and extends to a row that is different every week depending on how many people happen to be in the list.
I found something from this link that looks very similar to what I need, but with no knowledge of coding, I have no idea how it should be tweaked: Merge Duplicate Rows Keeping Data In Same Columns
I know I'm asking a lot, but the amount of time this takes to manually go through hundreds of rows of patient names every week is incredibly time consuming, and I have too many other things to stay on top of at work for this to drag me down day in and day out.
After sorting and filtering rows with in a set range I will have several rows that are almost duplicates. This is normal and expected due to how the workbook is used. Among these rows also will be several single rows that are not duplicates. It is important that I combine any two duplicates into one row. Example:
CREATE TABLES LIKE BELOW?
ABC D E F G H I J K
1 NameA 0XX15930777PS101300PS9
2 NameA0XX15930777PS91200PS10
3 NameX1159XXP555FBX1545PS9
4 NameB0A1234P123PS101263PS9
5 NameB1A1234P123PS90512PS10
What I need is this end result:
CREATE TABLES LIKE BELOW?
ABC D E F G H I J K
1 NameA 0XX15930777PS91200PS10PS101300PS9
2 NameX1159XXP555FBX1545PS9
3 NameB1A1234P123PS90512PS10PS101263PS9
It’s important that the data in each column stay with in that same column. Also of course it needs to be on the same row with the same person (NameA and NameB). The Columns that would determine if it’s a duplicate are D and E –. I would need this to be preformed via macro or some easy way so that others will not have a hard time. It will be on a protected Shared Workbook with Excel 2003. I've enclosed a Sample. How can I sort these or accomplish this and maintain the data where it needs to be?
I need to combine rows with that have the same column A value into one row. The duplication will be no more than 3 (ie 3 As, 2 Bs, 3 Cs, 1 D etc). I am looking for one of two options. 1) Ideally I'd like to be able to take 2 columns and turn them into up to 4 columns total. 2)Alternately, I can format the data to be in the correct columns to start, but will still need to merge the duplicate rows. The data can have any number of rows to start.
I've found something close, but it puts the new column data into ascending number order, which won't work for me because I need it to be in the specific order I input.(It also put all the data into one column, which I don't mind because I can do text to columns, but the order was the bugger.)
I have a long (about 180 000 rows) list of part numbers, descriptions, prices and quantities. There are many duplicate part numbers. I need to go through and find the duplicates, add all their quantities into one cell and delete the remaining duplicates. I have used conditional formatting to find the duplicates easier but cannot use the remove duplicates button as it will delete the whole row and I need the quantity information in each row.
Any formula or macro I can set up to search the part number column (F) for duplicates and add the figures in the quantity column (J) and THEN delete the duplicate part number rows?
Maybe I need to put the new combined information in another sheet?
I have an excel spread sheet with several rows of 265 (9A-IV) columns each with a heading. I would like to transpose the worksheet columns so that the heading is placed in Column A against the corresponding that is placed in column B. For example
ABCD14692571038Transpose to A1A2A3B4B5C6C7C8D9D10
I have dumped main frame data into excel. It is customer info that is in three rows of data each. I need to know if its possible to group or somehow keep three rows of data together for multiple sorts. The first row is the customer name, the second row is their first line of address, the third row is the city, state and zip. I need to add columns to this dumped data, but I will need to do a number of sorts for different views of the data. But as I've stated every 3 rows go together. I've tried different ways to group (for lack of a better word) the three rows, but nothing I've tried works. I've also done multiple searches, but either I'm not searching on the right word or this can't be done.
View 9 Replies View RelatedI have a spreadsheet that I want to keep the blank rows that seperate the data for asthetic reasons. Can I sort just the rows with data in them and still keep the blank rows where they are?
View 9 Replies View RelatedI'm trying to sort by City first, then by Report #, but keeping the highlighted rows together. If I use the custom sort it will through my D2's at the bottom and not keep them with their city, report, and D1. How do I get them to stay together? I have attached my document example.
View 4 Replies View RelatedI have an excel spreadsheet with approximately 10000 rows. There are approximately 10 columns...
Column A - person's ID number
Column B - name
Column C - Street Address
Column D - City
Column E - State
etc...
It being such a large list there are many many duplicates (it was pulled from a bunch of different excel spreadsheets and combined into one via copy and pasting one document into the other).
I figured out a way to have excel highlight the duplicates in a different color... but scrolling through 10k people and deleting the duplicates is obviously very tedious (approx 40% of the spreadsheet is duplicates).
Is there a way to sort out the duplicates (via their ID number, column A) so that there are no repeats? I want to get rid of the 2nd/3rd/4th (etc) occurance of the person's information but I was to obviously keep the first occurance.
I tried using Advanced Filter but I must have done something wrong because it shrunk the list too far down.
I have a spreadsheet with only 80 rows in it and I need data from another spreadsheet with over 200 rows. One of these rows in each spreadsheet contains a matching field (Job #). I only want the 80 matching rows data from the over 200 row sheet.
Is there an easy way to combine these two. Even adding the 80 to the 200+ will be fine as I can just remove the blanks. I can't figure out a way to combine them and match them up.
I have a spreadsheet with a lot of financial information going down a column (about 500 rows down). Most of the cells are just typed in, but certain rows have sum formulas in them. Is it possible to copy values from another workbook, paste the values in, but somehow keep the formulas that are running through?
View 1 Replies View RelatedI have an excel file with over 20 worksheets and each of them have around 1200 rows. The first column in each worksheet contains the variable names and then the data associated to it is present horizontally.
I only want to keep around 80 rows from those 1200 rows. They are not in sequence (means they are not in continuous order) so I manually selected those rows by deleting the non required rows step by step.
I did it manually on 2 worksheets but I don't want to do that manually over 20 worksheets. Is there any method that can speedup the whole process.
I am attaching the snapshots of the worksheets..
This one is before I deleted the unwanted rows.
This one is after the deletion of unwanted rows.
I need the macro for deleting duplicate rows only it should keep one lowest value row.
for e.g.
Column A Column B
TS1234 100
TS1234 50
TS1234 200
Macro will compair dublicates in column A & it will keep lowest value row (i.e. 50 value in Column B), other dublicate rows will be deleted.
I have a big amount of data and want to consolidate it.
I have identical text in Columns 1,3 and 4, but the text in Column 2 is different for all.
How do I make 1 row instead of 3 with Column 2 consolidated?
See the attachment : Excel_list.xlsx‎
I've attached a spreadsheet with a simplified example of my situation. Notes appear at the far right to explain my desired result. I've read through lots of posts on the forum but haven't yet found anything to mimic.
I'm building a calendar to display promotion activity by week for multiple customers and multiple products. The source data only lists one event per row, so the current result is not as concise I know it could be. I end up with twleve different rows for customers with activity each month on the same product, where I'd like to see just one row with all activity for the year.
Can anyone assist with either VBA or a formulaic solution? I've tried a few things with if/and statements followed by a concatenation of previous rows, but this seems like it will not have the intelligence to calculate only rows that match, I keep coming up short. Can OFFEST be used in some way?
I have 2 spreadsheets with a total of 100,000 rows of item descriptions. The problem is that I have to combine the text in the rows to create a complete item description into one cell. The number of rows for each description varies from 1 row to 7 rows. Excel experts here are using the term "concatinate."
There is one common element to the item description. There is a unique 11 digit code (in its own separate column) in the appropriate number of rows for each description. Note that the unique # I identified below doesn't look right since this document does not allow me to show the numbers in a column.
The spreadsheet looks like the following (I have created 2 item descriptions). I have at least 40,000 more.
PAD, EASEL, 27" X 34", ERASABLE, STATIC 61562030632
IMAGES, 34 SHEETS/PAD 61562030632
AVERY #24-391 OR EQUAL 61562030632
PAD, WRITING, 8.5 X 11.75, LEGAL RULE, 61562030940
PERFORATED 61562030940
AMPAD 20-___ 61562030940
RMOD FOR COLOR AND TO COMPLETE CATALOG 61562030940
NUMBER 61562030940
I could use a formula or a macro that would combine the descriptions into one cell. One other problem was identified as I tried to combine the text. There has to be a space created after the last letter of each row, otherwise, the last word in the row combines with the first word of the next row.
I have a list of data elements listed row by row with headers
last 4 of SSNFull Nameschool_codeterm_beginterm_endSchool Rep NameEmail
1111 Jim smith 00104600082012 082016 school reptestemail@hotmail.com
2222 Jane Doe 00104600082012 082016 school reptestemail@hotmail.com
3333 justin justin00104600082012 082014 school reptestemail@hotmail.com
4444 Joe Joe 00104600082012 082016 school reptestemail@hotmail.com
5555 Jake Jake 00104600082012 082016 school reptestemail@hotmail.com
What I need to do is to get all 5 rows into one row and duplicate the headers so row 1 has all fire rows of information one column after the next comlumn. If I can't duplicate the headers easily and can just aggregate the data into one row in cell by cell that fine.
Sample document attached for review
I have received a 'data dump' from a local county assessor. It is formatted for their use in displaying on their website. This particular file associates a parcel number with the legal description. However, with longer legal descriptions, they have replicated the parcel number and continued the description. The fields in the sample I have attached include (A) as the parcel number (which is common through all the files), (B) a county id, (C) the order that this specific portion of the legal description shoud be, and (D) the legal description, itself. In the sample you will see one specific parcel number that repeats itself more than 20 times, while other parcel numbers only appear once. What I hope to do is have one row for each parcel number with no replications. I hope for (A) be the parcel number, (B) be the county id, and (C) be the combined legal description. There should be approximately 275,000 unique parcel numbers.
A second option would be to move the lower continuations up, into the same row. For the example above refering to the 20 rows of legal description, I can work with 1 row and 20 columns(E through Z).
The provided coding was perfect, my problems were found to be the environment I was working. Once the data was prepared properly, the coding worked very well.
Not really sure what I want to accomplish is called but here's a description:
I've got a file with 2 columns
Column A Column B
C53445002558-001--104
C53445540.00
C53455002558-003--105
C53455540.00
C53465540.00
C53475002558-003--105
C53475760.00
C53485002552-002
C53485002558-003--105
C53487294.00
C53495002558-003--105
C53495540.00
What I want is the following:
C53445002558-001--1045540.00 ....
I have two spreadsheets of 27,000+ rows each.
Each one consists of user data for each time they logged onto our systems. The problem is that there is one row for each login. There are anywhere from 1 to 20 (or so) rows per user. I need to turn that into a spreadsheet with one row for each user.
In theory, the problem shouldn't be that difficult. Each user has a unique user ID, and the only math that's required in the merge is a simple adding of numbers from three columns to give a single total for each user.
So far, the only workaround I've been able to come up with is using the subtotal function based on the unique user ID which will at least isolate each user and total up their usage statistics. But it still leaves me with the problem of deleting the now uneccessary extra rows by hand, and then pastin the data back into a new sheet. Using vlookup, I think.
combining 2 rows that share 1 cell so that it is easier to format cells. For example: A4 and A5 have been combined with B4 and B5 to form 1 cell that has content. How can I convert this to a cell that only occupies A4?
View 1 Replies View RelatedI have a HUGE spreadsheet (1,000,000 rows) that contains a series of reports. The first row of the report contains all of the report identification numbers. The next 20-50 rows contains the report details, all in column A. Then the report ends with a cell in column A containing "[report_end]" and then on the next row the next report begins.
I would like to compress the report details all into a single cell in column A so that there is only one row for each report, sort of like this:
AA | BB | CC | DD
xyz
123
[end_report]
AA | BB | CC | DD
xyz
123
[end_report]
becomes:
AA xyz 123 [end_report] | BB | CC | DD
AA xyz 123 [end_report] | BB | CC | DD
I'm trying to automate a task at work which is really annoying. I've gotten some of the way, but would like your help to get over this hill.
This is what I've got so far:
I get the data from AutoCAD, I've cleaned it up, and created a Vlookup to give me the length in inches. Due to minor variations in length, the data from AutoCAD is seperated into equal lengths. What I need to do is combine this information into a single row. For example, rows 13-16 are exactly the same, and need to be combined into a single row. They are all 15" in Pour #3, but I have to add these numbers up with a calcultor to get a final count. Since most buildings are not the same, the data can be different, but they will always have the same basic columns; "Count", "Pour", and "Length". Name can be thrown out because it's not really necessary, I just keep it in there to make sure all I'm counting is lines.
I am generating a calendar view of project information in excel with the column headings as dates. Every project should be a merged group of cells spanning the rows for the proper dates.
Right now I generate a new row with new merged cells for every project (100 projects = 100 rows, even if the project only spans a single day!)
I need to be able to consolidate the project data so that if two projects' dates don't overlap they are put in a single row.
For instance:
Project 1 runs 8/2/2006 - 8/3/2006
Project 2 runs 8/5/2006 - 8/9/2006
Project 3 runs 8/6/2006 - 8/7/2006
Right now I get 3 rows, each with one piece of data shown as a group of merges cells.
I want to see Projects 1 + 2 or 1 + 3 on the same row (since they don't overlap).
Simply cutting and pasting up doesn't work because the vba automatically overwrites data.
I just don't know excel vba very well (long time access man).
RCA Page for forum.xlsx
I'm trying to get entries from one table to appear in another. The first table is like this:
DateDate
Customer Column1
Description Column1
Customer Column2
Customer Description2
Created By
The table I want to put the data into looks like this:
Date
Customer
Description
Created By
I have a sheet with over 1000 rows showing me every employee's benefit choices. It has multiple rows for each employee - one that indicates their health option, one that indicates their dental option and so on.
I'd like to combine them so I have one row that has columns for health, dental, etc...and then their options would be listed all in the same row. I've attached the original with the solution I'd like pasted below. I'm sure this isn't difficult but I'm having trouble figuring it out.
[URL]...I was trying to use this thread's solution macro on my sheet that is only consolidating columns A, B, and C with a couple thousand lines and I've had it running for over an hour now. There hasn't been any errors but is there any particular reason that might be causing it to take longer than it should aside from having to go through a few thousand rows?
Basically, I only want 1 Version of data in Column B and any other rows that has a match in column B, i want to consolidate unique data from Columns A and C.
A B C
AB 123 AAC
BA 131 AAC
AB 222 CAA
Res:
A B C
AB 123, 222 AAC, CAA
BA 131 AAC
I have a spreadsheet in Excel 7 for a lifelong cashflow model. The main worksheet is called 'Projection' with ages in rows & income (from a variety of sources) and expenditure (of various kinds) in columns fed with data from the Input sheet.
On the 'Input' sheet I have a button called Format Graphs which controls the following macro:
This isn't absolutely necessary, but being able to do this easily would cut down on a lot of tedious jobs for me. Let's say I have a set of data like this (I tried to space it out as best I could to give the idea):
View 3 Replies View Related