I need to combine data from multiple rows to single rows, for certain fields only. The database is large and can described as having an ID column, followed by columns 2 to n that do not need to be combined, followed by columns n+1 to N that do.
I've attached a sample dataset along with the desired outcome. For the purposes of illustration the sample uses n=3 and N=5 but the actual dataset has much larger dimensions.
I am trying to build a list of account numbers based on conditions in a spreadsheet. Every account number will look like: XXXXXX-XXX-XXX, depending on if there is a 'Y' in both fields...see attached. I am desperate to get this done and have hit a complete road block.
View 2 Replies View RelatedCountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83
[Code]....
left side pivot created in vb 6.0 & right side pivot table created manually in excel.
i want to generated pivot table using vb 6.0 same as right side pivot.
Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),
[Code]....
- The macro should then try to find this data on the worksheet "Sheet2" in the same workbook. This data on Sheet2 is essentially a table with four columns "Number" (Col A), "Country" (Col B), "Consol" (Col C) and "Bypass" (Col D) but can have around 70k rows
- It should look for Number first, and if its not found, the macro should insert the message "Update OK" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows and it matches the value input for Country on Sheet1, then it insert the message "Multiple Records - Update OK" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows and it does not match the value input for Country on Sheet1, and if all Bypass fields in Sheet2 have "Y" but the Bypass field input on Sheet1 has "N", then put "Multiple Records - Update OK" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows and it does not match the value input for Country on Sheet1, and if all Bypass fields in Sheet2 have "N" but the Bypass field input on Sheet1 has "Y", then put "Multiple Records - Bypass - Do Not Update" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows that have a Bypass value of "N" and it does match the value input for Country on Sheet1 then put "Multiple Records - No Need to Update" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is different for all the rows that have a Bypass value of "N", then put "Multiple Records - Refer" in cell A8 on Sheet1
[URL] ....
TestBook.xlsm
I have a table (is populated in a macro already) that has two header rows (1 & 2), inputs a blank row on row 3, then starts data input on row 4. The data can potentially run to row 1000. The columns that are used are B through M. The part name is in column B and the quantity is in column C. The part name can repeat with a different quantity and be put in a different row than a previously entered value. I need to combine those x number of rows based on the match of the part name and sum the quantities then delete the remaining matches. Columns D-M are not unique.
I have attached a sample. The sample is very simple with just two matches but the matches can more than two. The matching rows are highlighted. duplicate rows.xls
I have 500 rows of data with 6 columns. Columns A,B,C,D,E are text, and column F is values.
I'm looking for a formula where I can say for any of these rows where the data in columns A through E match each other in those rows, combine them into one row of said text, with column E values totaled. I thought I could use SUMIFS for this, but I only know how to define SUMIFS with the criteria being a specific cell/text/value for which it should search on. In my example, the data in the cells can be anything, I just need rows to be "collapsed" to one row for like data.
combining 2 rows of data if certain conditions are met. I have attached an example of my worksheet for reference.
Example.xlsx
What I want to happen is if a number in column J is followed by an "x" then the values in columns AQ and AR should be combined with the row of the same number (e.g. if 236x is in column J2 then the values in AQ2 and AR2 should be combined with AQ3 and AR3 because 236 appears in column J3, the same process needs to be repeated for 237x with 237 etc.).
I am hoping to combine repeated data into single rows. The best way for me to explain is to attach a spreadsheet with an example of how the data is and how I would like it presented. The spreadsheet has comments to highlight this.
View 3 Replies View Relatedso basically, here's an example of what I have. The spaces in between represent different cells:
Name Corporation Type Group
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 1
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 2
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 3
What I want to have is instead of having 3 rows in this instance, I want to have a macro that looks at rows and if it finds multiple rows with the exact same name, corporation, and type, it merges the rows to look like this:
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 1, Council 2, Council 3
In some instances, I have 3 rows that might match, some its 2, some its 10, it varies.
I have a set of data with 5 columns. The first column is ID. Now I have the same ID recorded several times with different data (in the other 4 columns) against the ID. I want to concatenate the data with the same ID into the same row. The data has already been sorted by ID. If it is the first time this ID appears, record the whole 5 columns. If the ID appears again, then record only the 3rd, 4th and 5th column. When I run my code, I got error 'subscript out of range (Error 9)' . It seems that the array I use has only 5 columns. But I don't know how to modify this.
VB:
Sub Patient_Detail()
Dim n As Integer 'index of rows to record to
Dim i As Integer 'index of column to record from
[Code].....
My business I work for is a collection agency and from time to time our clients send us files that are beyond recognizable for our collection software to understand nonetheless import into our database.
The file was a PDF at first but I have since been able to convert to excel format.
The data isn't too scrambled just scattered throughout rows. I would like to have rows of patient data into one row.
The file after being converted was all in row A but I have since used text to columns to divide into separate rows. However now one patient information is on multiple rows and I need each patient info on one row.
The file looks like:
|A---------|B-------|C----------|
1.John-----|Doe-----|123 Main St|
2.New York-|NY------|
3.11100----|SSN----|Balance Owed|
I would like the data from multiple rows to continue on row 1 like this:
|A--------|B-----|C------------|D----------|E--|F-----|G----|H-------------|
1.John----|Doe---|123 Main St.-|New York---|NY-|11100|SSN-|Balance Owed--|
I am no programmer in the least and have very little to no experience in pivot tables.
This file has about 7000 accounts with patient info spread through 3-4 rows for each set of data.
How I could go about doing this without manually copying and pasting into additional columns on the same row. That will take hours if not days.
I have a table with multiple rows for some employees and single rows for others. Each employee has a unique identifier.
Employee IDT1T2T3T4123Net salesmarket assetsmarginsspeed to market123Net salesturnover per quarterProfitOn time delivery112Net salesmarket assetsmarginsspeed to market180No. of ordersturnover per quarterProfitOn time delivery180unit salesturnover per quartermarginsspeed to market98No. of ordersmarket assetsmarginsturnover per quarter
Is there a way in which I can restructure the data so that the multiple rows for any single employee are added into subsequent columns in one single row? e.g.
Employee IDT1T2T3T4T1bT2bT3bT4b123Net salesmarket assetsmarginsspeed to marketNet salesturnover per quarterProfitOn time delivery112Net salesmarket assetsmarginsspeed to market180No. of ordersturnover per quarterProfitOn time deliveryunit salesturnover per quartermarginsspeed to market98No. of ordersmarket assetsmarginsturnover per quarter
The table has approximately 15000 rows & c. 30 columns in the original format. I can use basic excel, but I've never dipped into macros or anything very complicated...
I have a dataset that, due to the nature of how it is generated, separates certain orders into separate rows. Which orders these are is irrelevant, I just need a quick and easy way to make excel re-combine these rows in to one entry, and sum up the data in one particular column in the process of doing this.
The separate entries of a single order can be identified by the data in 3 columns matching - I have attached an example.
The constituent parts of the same order can be identified when the country, city, and code columns all have matching data as you read down. E.g., where
Country City Code
a 1 12345
a 1 12345
a 1 12345
Where this is the case, I want excel to automatically sum the count of shops for this order and then delete all but one of these rows, with the remaining row now containing this summed figure. Failing this, a new worksheet being generated/a new column giving me this data would be good.
Basically, we have 2 tablets tallying data and writing to the same .csv file. That csv file is then copied into a front page of a much larger excel worksheet where the tallied data is sorted and used for a variety of reasons. This works perfectly using index and match when only 1 tablet is being used, however, when both tablets are writing to the same csv at the same time it creates 2 lines instead of 1 line.
For example 1 tablet might look like this:
Lot 45689 4 6 8 9 1 5
2 tablets looks like this:
Lot 45689 2 3 1 8 1 4
Lot 45689 2 3 7 1 0 1
for the tallies, i would imagine a =SUMIF function might suffice, but that doesn't solve my problem of there being 2 lines with the same lot #.
I am only dealing with this issue from the excel side, and have no control over the tablet functionality or the CSV file which further limits me.
Wanted to add that the Lot #s change daily and often so with my understanding of pivot tables this rules them out as a viable option.
I need to Align some address fields that I receive from an import. I have several lines of data that are represented in the attachment in Sheet 1. I'm just looking to clean it up a bit by having the address be listed in one column. I'm hoping to acheive the results that appear on the DesiredResult sheet. I could have sworn I saw a similiar post several weeks ago while perusing Ozgrid, but now that I'm in actual need, I can't seem to find it when searching.
View 3 Replies View RelatedI have 4 colums of customer input data, Length, Width, Height and Quantity. There are over 20 rows to accomodate multiple combinations. I have several other hidden pricing pages with all possible combinations including materials, pricing etc. Assuming I am on the frst pricing page and first combination I need a formula that will match the first 3 inputs per row and if true give me the 4rth input for the matching row.
Example:
Customer input-
row 1 = 8 - 12 - 4 - 15
row 2 = 4 - 4 - 2 - 25
etc.
Order Page-
Combination 1 = 4x4x2 quantity___ (input row 2 matches 4-4-2 give quantity 25)
Combination 2 = 4x8x2 quantity___ (no input row matches 4-8-2 give quantity 0)
Combination 36 = 8x12x4 quantity___ (input row 1 matches 8-12-4 give quantity 15)
I have data as follows -
OrderTDF
1gc3
1gc3
2du3
2du3
2gc3
3du3
3du3
3du3
and want to combine lines by order so that data now becomes this -
OrderTDF
1gc3gc3
2du3du3gc3
3du3du3du3
I've tried to combine rows, but can't seem to do it. I have a spreadsheet with over 18,000 lines of data, and I really don't want to go through each line to add information from another line and then delete that line. Is there a formula that can do this? I have attached a sample of what I have and what I need.
View 8 Replies View RelatedI have just two columns, one with numerical codes (A), the other ones with text (B).There are duplicate codes with different values, for which I would like to be able to combined their text values on a single row.
A | B
xx | text 1,
xy | text 2,
xz | text 3,
xz | text 4,
xu | text 5,
...
to be formated to:
A | B
xx | text 1,
xy | text 2,
xz | text 3, text 4
xu | text 5,
...
The resulting column B can be with/without space delimiter between the text values (there is a coma after each text value)
I have a table which has employee names on the left handside. Next to them are contacts in a region.
View 7 Replies View RelatedI have a data that looks like this
P50101
181.0
89.2
19.94
12/18/06
KK
P50101
177.0
75.9
22.26
11/26/07
KK
[Code] .......
I need to enter this into spss so I need it to look more like this e.g. grouped by the first column
table { }td { padding-top: 1px; padding-right: 1px;
padding-left: 1px; color: black; font-size: 11pt;
font-weight: 400; font-style: normal; text-decoration: none;
[Code] .......
I have a 8500 row sheet with 40 series of data given for each "data set". This data is situated in four columns and ten rows, so that rows 1-10 contain one data set, rows 11-20 contain another data set, etc...
Does anyone have a simple VBE script that can grab this data and format it so that my data sets are arranged as 850 rows and 40 columns instead of the native 8500 rows and 4 columns?
I need two solutions, if possible. One macro and then a "poor man's way" in case the macro breaks down the road. something where I can do it with copy/paste, formulas etc.
I will just use letters and numbers to make it easer.........
Column A might have 10 rows, with 1,2,3,4,5,6,7,8,9, and 10 as the data.
Column B might have 7 rows with A,B,C,D,E,F and G as the data.
I need column C to have 70 rows, with 1A, 1B, 1C, etc. .......through 9E, 9F through 9G as the data.
I am having 3 rows with same address and want to combine rows with equal names into single and delete remaing two rows
View 3 Replies View RelatedI have created a pivot table that is connected to an input sheet with data. The input sheet retrieves data automatically from a external source through an add-in to Excel. When updating data the fields expands, but only for the items which have been changed. I want the table to be updated automatically, but not the fields expand automatically. Is there any pivot options to prevent this problem?
It should be mentioned that the pivot table is not directly connected to the input sheet (which is updated from the external source), but from a "help-sheet" reflecting the input sheet with some additional columns. I use conditional formatting and name range in the pivot.
I am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.
View 2 Replies View RelatedI have a spreadsheet that contains two input tables: Parallel and Perpendicular. Next, the user can select a group which a certain entry belongs to. Now what I want is a "summary" of these entries in a table that has no blank rows and combines both Parallel and Perpendicular entries by the Group. Please note that there is no fixed number of inputs for any one group i-e the solution has to be dynamic.The groups are numbered from 1 to 6 and number of groups is fixed i-e 6.
A sample file illustrating the inputs and required output is attached.
I also posted this on: [URL] ....
Sample_file_summary.xlsx
I have a list of about 4,000 contacts each with about 2 or 3 products with us. Each product creates a new contact row.
I would like to merge each row with the same contact into one row.
I have attached an example : Example test.xlsx
I'm importing some data from an HTML table into excel, it all formats pretty well except that some of the data appears on multiple rows.
A B C
1 01.02.06 John
2 Bob
3 Sam
4 02.03.06 Jim
What I need:
a b c
1 01.02.06 John, Bob, Sam 3
2 02.03.06 Jim 1
What I need to do is get all the items in the colum B which are listed under the same item in column a, in the first case John Bob and Sam and put them all into the same cell. I then want to delete the empty rows. It's important that this is done by testing to see if column A is blank, rather than using the date.
then in column C I want to count the items in column B,
ideally then repeat the script for the whole sheet.
Not a clue of the type of syntax I should be using though!
In fact there's probably a better way of importing the data from the HTML so it doesn't even need to be done this way! of course I'm oblivious to it. Auto Merged Post Until 24 Hrs Passes;Thinking maybe I didn't make myself properly clear....
If a cell in column a is empty then it means the item in column b needs adding to the end of the item in the row above in column b.
I then want to count the items in column b.
I am trying to do is when a user enters in a GL (a 4 digit number) from the list (tab 2) I want it to appear in the summary box below. However the same GL number can be used multiple times so I will have duplicates. The data entered above will always change there is over 200 possiblities the user can enter. So I don't want the data to be specific it will populate based on what is entered by the user.
Can this even be done?????
Say if the user entered:
Column C Column G
4606 $20.00
4606 $20.00
2134 $15.00
2301 $35.00
4606 $100.00
The data in the GL and currency column in the summary box would appear as the following:
Column C Column G
2134 $15.00
2301 $35.00
4606 $140.00