Grouping Data From Similar Fields

Jan 4, 2010

I have a set of data that looks like this:

A | B | C | D
---------------
100 | 2 | 0 | 0
100 | 0 | 0 | 7
100 | 6 | 0 | 0
101 | 8 | 0 | 0
101 | 0 | 5 | 0
101 | 0 | 0 | 1

Column A is a 'User Number' and columns B-D contain values.

I was hoping to use this data to create a new range where each 'User Number' only has one row, and the values in the other columns are summed for each user.

This would ideally look like this:

A | B | C | D
---------------
100 | 8 | 0 | 7
101 | 8 | 5 | 1

View 9 Replies


ADVERTISEMENT

Convert Column Data Fields To Row Data Fields In Pivot Table

Feb 8, 2014

CountryHourDataTotalData
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]....

View 2 Replies View Related

Comparing 2 Columns Have Similar Data - Extract Matching Data

Apr 18, 2013

So I have this problem in excel with comparing 2 columns.

Basically, I have 2 columns(a &B) that I need to compare with one another and find out the matching data. I am trying to use that matching data to enter in our reports.

Both columns might have duplicate items because I am dealing with premium numbers.

Formula to compare these 2 columns and find the matching data and extract it to a separate column. Keep in mind, i am dealing with almost 20,000 lines of data.

View 2 Replies View Related

Rows Similar Data Divided By Blank Row To Delete Data If There Are 6 Or Less

Apr 2, 2014

I have a lot of rows of data all divided with an empty row and if they are 6 or less all rows need to be entirely deleted any 7 or above need to be kept.

View 2 Replies View Related

How To Merge Similar Data

Jan 23, 2013

I have a large spreadsheet where some data are similar. for Example

Robert $1000
Louis $600
Glen $350
Doreen $2300
Robert $500
Louis $600
Glen $750
Doreen $300
Robert $3000

I want to merge all Robert together, all Louis together and show as one Robert $4500. How can I do that ? Should I use a Look up.

View 11 Replies View Related

Using Borders Where Data Is Similar

Apr 17, 2007

I have a spreadsheet that has descrptions in column D. Where the descriptions are the same ,

I need the VBA code that will do the following:

1) Sort the data by columns C (descending), G (ascending) & E (ascending)

2) Putting a border around all the data where the decriptions are the same
i.e from column A to Column I beginning from row 2

I have attached a sample file for ease of reference....

View 9 Replies View Related

Gather Data Across Several Similar Sheets

Mar 3, 2014

This particular formula is used several times to gather various data points across 3 different sheets named Month-1, Month-2 and Month-3.I gather the data the following 3 formulas then sum it as needed with a helper cell.

=IFERROR(INDEX('Month-1'!$A$1:$G$6000,MATCH(TRUE,INDEX(1/('Month-1'!$A$1:$A$6000=$G$24)*ROW('Month-1'!$A$1:$A$6000)-MATCH($A96,'Month-1'!$B$1:$B$6000,0)>0,),0),MATCH($M$2,'Month-1'!$A$14:$G$14,0)),"")

[code]....

View 2 Replies View Related

Merging Similar Data And Amounts

Feb 22, 2013

Column A: List of document numbers
Column B: List of amounts

A B
111 $5.00
222 $10.00
111 $5.00
222 $15.00

I need to merge all equal document numbers so that it shows the full amount on that document number. It needs look like this in the end:

A B
111 $10.00
222 $25.00

I realize a pivot table will do this but the formatting of a pivot table will not work with what I am ultimately trying to do.

View 7 Replies View Related

Selecting Groups Of Similar Data

Aug 5, 2009

I have ~1,000 rows of data utilizing columns A:C. The data is "grouped" by data that is in column C. Layout is as follows for example

Row01.....Column A......Column B.......Column C
Row02.....some data.....some data.....apples
Row03.....some data.....some data.....apples
Row04.....some data.....some data.....apples
Row05.....some data.....some data.....apples
Row06.....some data.....some data.....oranges
Row07.....some data.....some data.....oranges
Row08.....some data.....some data.....oranges
Row09.....some data.....some data.....oranges
Row10.....some data.....some data.....pears
Row11.....some data.....some data.....pears
Row12.....some data.....some data.....pears

I have been trying to come up with some code that will "select" groups of data and then perform an action on those cells. As an example:

View 5 Replies View Related

Merging Cells With Similar Data

Mar 2, 2005

I am trying to merge two cells that have the exact same content in each cell. Everytime I do this, I get a pop-up warning that Im trying to merge cells with content in them and that only the upper-left content will remain. This is exactly what I want, without that pop-up, as I have to do this for multiple columns at a time.

View 2 Replies View Related

Comparing Two Similar Sets Of Data

Apr 30, 2014

I've got a pretty tough problem I need to solve with excel. So im comparing prices of products from 2 Different Sources, I need the best method of comparing these sets of data. Realistically I need both sets of data to compare and move automatically (or create duplicates elsewhere) to show the product ID, Price A and Price B.

Here's an example

Supplier A
Cheese Grater: 39.99
Golden Spoon: 129.99

Supplier B
Cheese Grater: 59.99
Golden Spoon: 89.99

These values will then combine to read

Supplier A Supplier B
Cheese Grater: 39.99 59.99
Golden Spoon: 59.99 89.99

Not sure if it will matter but both lists are not identical, all I want are the matches not the ones unique to different suppliers.

This comparison list is like 12,000 products long, I'll never be able to do it manually. This has stumped me for a while now I only seem to be able to find formulas which give TRUE / FALSE or Colour changes.

View 1 Replies View Related

Two Columns Have Data Is Not Similar Then Return The Value

Jun 13, 2007

I need a formula that looks at 2 columns and if the data is not similar to return the value.
For example
Column A
joe Bloggs

Column B
Bloggs, joe

Column C
if value in column B is not similar to column A to return value of column B

View 9 Replies View Related

Combining And Expanding Workbooks With Similar Data?

Jul 13, 2012

I have to compare and combine data from multiple files and combine it into 1 master spreadsheet. I have attached a sample file. It is in bulgarian, but the language is of no importance. An example of the file: it contains data about repair maid on a truck. First column is just the number of the repair type and shall be filled upon completion of the table. the second column is the name of the repair itself. the 3rd column is the date the repair was made. the 4th column - the total work hours the repair was made. Some repairs were made more than once and thus the merged cells 3 to 9 in column B. I have 30 trucks which have more or less the same repairs, just a different number of each repair. A sample file of 1 truck is attached, called 1truck.

The master spreadsheet should be of the same type as the example, however showing data for all trucks (from different files) next to each other. (the attached "alltrucks") As you can see there are repairs done only on some trucks, and others are done multiple times. The master sheet should have all possible repairs from all trucks. So far I've been doing it manually, but takes way too much time...

View 1 Replies View Related

Fastest Way / Shortcut To Add Similar Data To A Chart

Nov 28, 2013

I have a plot of data in a chart. I've added a worksheet to the same workbook which has data that I want added to this plot. The data is in the SAME columns/rows, the only difference is the worksheet name. I'm looking for the quickest way to add the data to this chart as I have to do this many number of times. Perhaps if possible to somehow copy the

=SERIES("Title",sheet_name!$A$11:$A$18882,sheet_name!$R$11:$R$18882,5)

And paste it back in with just a sheet name change?

View 5 Replies View Related

If Statement (sort Data Similar To Below Using The Following Conditions)

Oct 26, 2008

I need to sort data similar to below using the following conditions. If the total of A's is greater than 4 they get an A. If the total of E's is greater than 4 they get an E. If they have more A's than E's, assuming they have at least 4 of each then they get an A. If the total is 5, but they don't have 4 A's then they get an E

NAME E'sA'sTOTAL
John 235 ( so this one should get E)
Mary 459 ( This one should get A)
Tim 112 (This one shouldn't get anything)
Jane 145 ( This one should have an A)
Sam 415 (This one should have an E)

I would like this to sort in one cell - I can do this over 5 cells - but not in one.

View 3 Replies View Related

Vba To Match Multiple Similar Data Question

Mar 12, 2009

I have a worksheet (titled "Data") that has data in columns A thru

Q. I'd like to create a command button that when activated would search column data from cell Q2 down and then identify any matching data that has 30 or more similiar matching entries. I would then need this macro to copy all of the row data across for all of the matching/applicable data and then copy it beginning in cell A2 onto a worksheet titled "Search Details". The difficulty is that filtering won't work as I need the macro to loop until it can identify any/all data that has 30 or more matching data. The type of data that would be in column

Q2 and below would be something like "Doe01/01/01", "Smith02/03/27", etc. Both the Data and Search Details sheets have headings in row A1 thru Q1.

View 14 Replies View Related

Formula To Find Similar Data In Columns

Feb 17, 2007

I need help creating a formula to find data in one column that starts with different letters and counts them up.

For example one column would contain the words amoung others:
DPDE
DNPD
EPRO
EOTH
YBRC
YUND

In the other column I want to count up the amount of times a word starts with the letter D or E or Y. The count comes from another spreadsheet, which the CMRF Tool (See attachment) is pasted at the bottom of.

The Monthly case report file is the file I want to get the count from. These would come from Column O once the data is there. The count would end up in column D of the CMRF tool which is pasted at the bottom of the monthly case report file.

View 9 Replies View Related

Grouping Some Data In Bar Chart

Jul 29, 2014

I have some cost data for 5 different technologies that I would like to plot in a graph. the technologies belong to 3 unique categories and I want to group them on the chart so that there is no gap between technologies in a same category.

I have a graph where they are grouped by title of the category but how I may remove the gaps between the individual bars in the same category?

View 4 Replies View Related

Data Protection And Grouping?

Feb 12, 2013

grouping the protected/locked data.I am using excel 2010.I have data in sheet and have inserted group. I have protected this sheet and hence not able to use data group in this sheet.Is there a way i can operate data group option even if the sheet is protected.

View 1 Replies View Related

Grouping Data Into Categories

Jun 9, 2006

I have a given set of data. Each row of data fits into one of 7 different categories. Besides doing a simple filter to be able to pull out a certain "category", how can I do this differently? Is there a code/macro process I need to go through? Basically what I need to do is find a way to group these categories together, so that someone searching for a certain category can easily pull it from the data.

View 8 Replies View Related

Filter Data Between Two Dates With Customer ID And Sum Similar Products

Apr 1, 2013

I have problems with my project. I want to filter data between two dates with customer ID and when click Export Report button , result will copy on next sheet ( sheet's name is report in my file attachment ) and auto sum similar products.

eExample_report.xlsm

View 1 Replies View Related

Automatic Background Color In Cells With Similar Data?

Nov 29, 2012

I, daily get a list of individuals with some data against each one of them. E.g Amit would appear 7 times in the list, John would appear 10 times in the list and so on and so forth. I am required to sort the data as per names and then fill one background color. One color for one person so that it becomes easy to read data specific to an individual.Ia there a way that the color fills up automatically. note the names and number of entries are not same everyday.

View 8 Replies View Related

Collect Data From 4 Similar Spreadsheets And Rank Them In Fifth Spreadsheet?

Sep 24, 2013

i am trying to do collect data from 4 different spread sheets (they all consists of the same columns but they do not have the same amount of rows) and rank them based upon one of the columns in a fifth spreadsheet. in the fifth sheet i also want to display all of the information found in the four sheets, see simplified example below. Is this possible? and how do i do it? when i googles it i only found ways to do it using macro but i know nothing about macros..

Simplified example:

Sheet 1
A 3 W
B 5 X

Sheet 2
C 2 Y
D 7 Z

What i want excel to do
C 7 Z 2
B 5 X 1
A 3 W 1
C 2 Y 2

View 2 Replies View Related

Extracting Similar Data From Different Worksheets And Displaying On One Sheet?

Apr 13, 2012

I have two work sheets as-

Sheet1

idnameclassscores
1abc280
2efg276
3hij555
8klm478
9mno490

Sheet 2

nameschooldate of admission
abcpublic school2/9/2011
efgpublic school3/4/2010
hijprivate school5/9/2011
klmprivate school8/9/2011
mnoprivate school9/10/2011

now what i want is - on sheet 3 compiled data as-

idnameclassscoresschooldate of formation

View 1 Replies View Related

VBA To Sort And Consolidate Similar Items From Imported Data

Sep 6, 2013

We have a software program that we use to takeoff HVAC equipment on drawings. It will export all of the items to an Excel worksheet. Once it is into Excel I need to sort the data by two or more different columns, then sum three to four columns for each unique item for transfer to the equipment sheet for pricing. I've searched this site and others, but have not found a way to do this with VBA in Excel.

Floor
Rank
Name
Description
Qty
Cost
Labor
Seismic

[Code]....

The sheet is a simplistic view of what I'm trying to explain, obviously in high rise there will be many floors and hundreds of items. The actual spreadsheet has approx 12 columns, but only 3 or 4 need to be summed per similar item. Most times I sort by Range & Name. On occassionwe need to sort by Floor, Range & Name. Used to do this with a Database & RR Report Writer, but the new software does it onscreen with digitized drawings.

View 2 Replies View Related

Grouping Data Into Defined Bins Using VBA?

Oct 18, 2012

I've about 30,000 recordes in Column A of numbers between 1 and 100. In Column B I'm looking for a formula to return "Less than 10", "10-20" etc up to "90-100".

I think I need to use excel vba If...Then...Else

View 2 Replies View Related

Grouping Data In A Pivot Table

Oct 31, 2008

from the Forum on building a macro and was kindly told that a Pivot Table would do the job better, which it does. However, my table has a column of several dates, I was wondering the best way to group the dates into months rather than showing the full date, eg, September instead of 16/09/08 or August instead of 01/08/08.

View 4 Replies View Related

Grouping Data In Pivot Table

Mar 28, 2012

I'm looking for a quick way of counting the proportion of records in certain number ranges e.g.

Number of records in range 1 to 50, 51-100, 101-500 etc.

i have a pivot table but I'm just not sure how to create these groupings. Is a pivot the right way to do it - or should I be doing something else with the source data.

View 4 Replies View Related

Data Sort Grouping By First 4 Digits

Jun 1, 2012

I'm trying to sort a selection of data into groups (2 cols, 1st is P-No, 2nd is Qty) based on the first four digits of the P-No and then tally the groups Qty (so I have the total Qty for any given product group) but short of sorting the data by P-No then manually separating it into groups where the first four digits are the same and then applying a SUM calc to the separated groups Qty column I can't think of a function/sum that would speed this process up!

Any way to speed this process up/automate it?

View 7 Replies View Related

Grouping Of Data List Into New Worksheets

Oct 20, 2013

The Data Worksheet in the file attached lists information on various software packages a company sells. Each product has an associated category listed in column B. Write a sub that creates a worksheet for each category represented in the list, with the name of the worksheet being the category, such as Business. For each category worksheet, it should enter the product names and their prices in columns A and B, starting in row 4. Each category worksheet should have an appropriate label, such as “Products in the Business category”, in cell A1; it should have labels “Product” and “Price” in cells A3 and B3; and the column width for its column A should be the same as the column width of Column A in the Data worksheet. ( Note that there are only three categories represented in the current data. However, the program should be written so that it Works for any number of categories – and any number of products- that might be present.)

I have the following code, it works properly when I run it for the first time but fails to re-run for the other coming runs.

This is what the data sheet looks like: [URL] .......

Code:
Sub Business()
Dim Data() As String
Dim blnCheck As Boolean
Dim intProdCnt As Integer
Dim i As Integer
Dim wksC As Excel.Worksheet

[Code] ......

View 5 Replies View Related







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