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....
Trying to select all cells below the last row of data based on column B and remove borders.
So far I have this.
[Code] .....
I have code that selects and copies data from one sheet onto another sheet. the sheet that is getting data copied too has borders defined. When i run the code i get a debug error. Here is the issue.... when i remove all the borders it works fine. I have tried everything i can think of to solve the problem but have had no luck. anybody have an idea what is causing this. i attached the workbook file so you can see what is going on.
View 2 Replies View RelatedSo 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.
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 RelatedI 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.
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]....
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.
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:
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 RelatedI'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.
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
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
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...
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?
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.
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.
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.
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
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 Relatedi 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
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
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.
Sheet 1
Sheet 2
UPC
Sku
[Code].....
I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3
And if it doesn't find anything just return Not Found
The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above
I have attached the file, Problem.xlsx
How I will populate data of Weekday through the cells.
Is there a formula or function to populate data.
I am trying to create a macro, that ultimately does the reverse of one written a while back.
I have created a macro that exports "Roster_Data" to "envision_Roster" in .csv style formatting. What I want to achieve is reverse engineer the macro to return the data back to a similar state.
I am trying to transpose column D into rows that correspond with dates in column c in a sheet called "OutputView" this in essence is similar if not exactly the same as the original worksheet "Roster_Data"
I have split the macro I am working on into three separate modules.
Module 1 - Initial Export of "Roster_Data" to "envision_Roster"
Module 2 - Format and output worksheet to find MAX date and MIN date and produce top rows of data
Module 3 - Analyse, Undertake Logical Tests of data, and transpose to suit (Replicate the initial "Roster_Data" view)
I'm having trouble visioning this altogether so I've been starting with basic code to output basic stuff, but I still can't relate this back to my data.
[Code]....
i have multiple worksheets which has data (similar to sheet "Homewares") which need to be summarised in the summary sheet, i need macro that will grap all the info from all worksheets and pull data into sheet summary. The headings in data sheets will always be in Col A, i need to populate the summary sheet with this data please see below example
Homewares
AB1 2 3CompanyHomewares Limited4 5Register NameInvestment Services Pty Ltd6 7CodePCABO1 8 9Sec TypeFPO10 11Option Expiry Date 12 13Total Holdings5,947,500.00
Excel tables to the web >> [url]
Macro to return result as per below screenshot
Summary
ABCDEFG1 2 3 4 5 6CompanyRegister NameCodeSec TypeOption Expiry DateTotal Holdings 7Homewares LimitedInvestment Services Pty LtdPCABO1 FPO 5,947,500.00 8 9 10 11
Excel tables to the web >> [url]
I have a spreadsheet containing data in coloumns A to L.
Where all the codes in column B are the same then a border must be placed around all the codes that are the same i.e the border must start in Column A and end with column L.
provide me with the code the will put borders around the codes in column B that are the same ...
I have created a 4 page chart in landscape with text in each of the 4 columns in the chart.
When I looked at it in print preview the chart did not fill the page and so to widen the last 2 columns I dragged them over to the right hand margin. I am still definitely within the right hand dotted line showing the margin limit.
The problem is that I cannot now put a border line down the extreme right hand column. Every time I click on any of the border instructions in the Font tab nothin appears on the right hand side of the box.
I am trying to write vba code that will highlight the row in the range if a field is over a certain percent. The column number won't change but the number of rows will. I'd also like the code to automatically work on all tabs of the workbook when a button is clicked.
Data Info:
Currently there are 4 tabs, but can have more/less
Columns used are A:O
Data for shading starts at A3 and should go to however many rows have data and ignore blank rows
Formula should be if data in column E is over 10.00% then the data in that row A:O should be shaded in the color off yellow & have black thin orders
If the data in column E is not over 10.00% then the data in that row should not be shaded but still have thin black borders.
