I'm trying to compile a VBA that would allow me to compare 2 columns "A" in different worksheets (same Workbook) and output any unique values to 3rd worksheet together with the rest of the values in the corresponding row.
Sheet1
A
B
C
[Code]....
Excel 2010
How i could this one in formula or macro, first my 1st sheet is just as show below (maybe use for a template)
1st sheet (full list)
fruits
china
orange
0
0
0
0
fruits
china
apple
[Code]...
then 2nd sheet, report i get which something like this :
fruits
china
orange
1
25
1
500
fruits
jiapan
kiwi
2
[Code]...
then i want result like this on 3rd sheet :
fruits
china
orange
1
25
1
599
[Code]....
so basically i want add missing row on sheet 2 from sheet 1
I am using office 2010.
I need a macro to compare the values in column b across 2 sheets and output the rows that do not have duplicate values in column b to a third sheet?
View 3 Replies View RelatedI am looking up the largest value across various sheets (1 to 99) with the following formula:
=LARGE('1:99'!$B$1:$B$50;$C3)
That correctly returns the largest value in range B1:B50 across sheets 1 to 99.
However now I want to know the sheet name of the value above in a seperate cell, let say in: A3.
I'm using excel 2010.
I have a worksheet with five columns (A, B, C, D and E)
The cells in Column B contain letters and/or numbers (without spaces) in no particular order.
The cells in Column C contain letters and/or numbers (without spaces) in no particular order.
I want to compare all characters in 1st Cell of Column B with all characters in 1st Cell of Column C, and display the matching characters in 1st Cell of Column D, and the character count of 1st Cell in Column D must be displayed in 1st Cell of Column E. note that multiple instances of the same character must not be treated as duplicates. When execution on 1st Row is finished then repeat procedure for Row 2, etc... Stop execution when first empty cell in Column B is located.
Example:
B1 = LJLM12
C1 = KY2MLK
B2 = ZCG4GM
C2 = X4GGGC
B3 = KTCBNG
C3 = GNBTBB
The script/code/formula must output the following:
D1 = LM2
E1 = 3
D2 = CG4G
E2 = 4
D3 = TBNG
E3 = 4
I am using Excel 2003. Y
I have a database with 2 un identical columns: A, and B. Each cell in Column A should have an equivelant cell somewhere in column B. I want a code that could compare each cell in column A with each cell in Column B. Cells in A that don't have equivelant in B should have their values printed in column C.
View 4 Replies View RelatedI have 1 workbook, with 3 sheets. Sheet1 (EVER) has 3000+ rows and 12 columns of customer information. This sheet is for all customers who have ever placed an order. Sheet2 (06-07) has 1500+ rows and 12 columns of customer information. This sheet has all customers who have placed an order in the last 2 years. Sheet3 has 1 row, which consists of the column titles (12 columns) that are on Sheet1 and Sheet2.
I need to put all customers that are on Sheet1, but not on Sheet2 in Sheet3. I have tried VLookup; advanced Filter and a number of codes in the last 3 days and have not been able to figure this out.
I'm working to compare to sheet every days and i have been doing this for 2 years
I need to write macro to compare 2 columns from two sheets (sheet1 and sheet2) on same excel book and display the result on the sheet2. The number of rows and columns may not be equal and it can vary each time.
The result should be written in the sheet2 at column 'NOTES'
If serial number is blank, the result written at NOTES column =*NOT TRACK
If serial number found on sheet 1-serialNumber column, the result written at NOTES column =*Registered
If no serial number found on sheet 1-serialNumber column, the result written at NOTES column =*New registration
Example Sheet1:
PurchaseOrderNo
Manufacturer
ProductNumber
SerialNumber
PO123
Table
453154-B21
[Code] .....
I'm using excel 2010 and window 7.
Excel 2010, I have the following list (showing part of it):
Name
T-Shirt Sizes
Nathan
S
[Code]....
The aim of the list is to see what Sizes each and every person needs and then count how many T-Shirts are required for that particular size. The list is over a 100 entries long so counting it manually isn't really an option as it's time consuming as well as errors might arise.
Thus, I've been trying to use the IF statement with the following logic but to as no avail
IF Size in Column is XS
Add 1 to a particular Cell
IF Size in Colum is S
Add 1 to a particular Different Cell
and so on for the other sizes...kind of like a counter for a for-do-loop.
Here's how my daily report is currently set up:
ExternalInternalDifferences
123000123000
234564234564
345456345456
456567378987
565456456567
654547524564
788879565456
865478654547
745654
788879
865478
The External column contains data provided by an external vendor. The Internal column contains data compiled internally. The data in the Internal column will always contain data duplicate to the External column, but will also contain unique data not present in the External column. I would normally cut out the data contained in Internal column that is not duplicated in the External column, and paste it into the Differences column, and move the remaining cells in the Internal column up. The result would be the External column data and Internal column data would align by row, and the data unique to the Internal column is segregated to the Differences column, like so:
ExternalInternalDifferences
123000123000378987
234564234564524564
345456345456745654
456567456567
565456565456
654547654547
788879788879
865478865478
While not a difficult task for 8 rows of data, the actual daily report contains over 1000 rows, on average. I would like to set up a template workbook, where I can simply open the template workbook, paste the data into both the External and Internal columns, then have the values that are unique to the Internal column extracted from the Internal column and inserted into the Differences column.
The end product would be used daily, with differing amounts of data (from 100 rows to 10,000 rows).
I have 2 columns directly next to each other each containing dates.
Example 1:
F2 = 5/23/13
G2 = 5/23/14
Example 2:
F3 = 6/6/13
G3 = 4/11/14
I wanted to do a conditional formatting that would highlight the cell in
column G if the Month/Day doesn't match the Month/Day in column F
Is there a way to do this with Excel only looking at the Day/Month and ignoring the year??
I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)
The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.
Sample data Required Output
A | B Z
1| 10 | a 10,a,b,c
2| 10 | b 11,a
3| 10 | c 12,a,b
4| 11 | a
5| 12 | a
6| 12 | b
My system is Windows 8 Excel 2010.
I need a macro that will compare two sheets, find the differences and produce a new third sheet called, results. Both sheets to compare will differ in length of values (one may have 10 numbers to compare, and the other could have 50, Ive attached sample data), and thus this calls for a primary and secondary sheet. I would only like the values which appear in the primary and not the secondary sheet to be reported in the new results sheet.
The problem is that the values never match up, but they are always within a +/- 0.5 window of each other. It would be great to compare the two sheets, to produce a list of the values which appear only in the primary sheet and not the secondary. Im trying to get this to link up to a userform, where the user select the primary sheet (A) and the secondary sheet (B), selects the tolerance, and produce the results sheet (see attached).
I've got two worksheets ("June" & "July"). On both worksheets, column A is comprised of ID numbers and column B contains dollar amounts. I need to compare the ID numbers in Column A on each worksheet, and if they match I want to copy the ID number and the amount to a third worksheet ("Results").
View 3 Replies View RelatedExcel 2010ABCDEFGH1DATESAMPLECUSTOMERSHADEREF #ARTICLE"BRAND"REQD2REQ # CONES302.01.139118CNS-BACLAS-40463-2TEX-60-2000M PP(29/3)SILVER FALCON2402.01.139118CNS-BACLAS-40463-2TEX-30-3000M PP(40/2)SILVER FALCON2502.01.139118CNS-BACLAS-40463-2TEX-27-3000M
[Code] ......
I have like above 12 sheets (JAN-12 to DEC-12). I want to make a summary in new sheet base on bellow condition.
All the 12 sheets , data need to copy to new sheet & same time if Column "C,D & F" values are repeating in same sheet or any other sheets then that repeating rows column " H" values should summarized (only one entry should display in summary).
I have a workbook with 2 sheets. Both the sheet contains Column "Name,Avg,Max". Compare both the sheets and paste the Avg values in Sheet3(Avg) and Sheet4(Max). I have attached the sample file below.
comparison.xlsx
I have data set up like the example but then for 1797 lines.
D39BKYes
D39BKYes
D39BKYes
D39BKYes
GHFLBNo
R80FANo
R80FANo
[code].....
What I need to get in a pivot table is: two columns or rows (Yes / No) and the unique count of the code.In this case is should show Yes: 2 and No: 3
I have a wookbook with serial numbers in column A and barcodes in column B. There are 51940 rows in total.
Is there a way I can search through column A, and for each unique value found , concatenate all of the barcodes that relate to that serial number in column C?
Serial Numbers | Barcodes | Combined Values
-------------------------------------------------------
abc123 abc12300 abc12300,abc12301, etc etc
abc123 abc12301
def456 def45600 def45600, def45601, etc etc
ghi789 def45600
i am using Excel 2010.
How do count unique values in category in pivot table. (my table, im taking data from ms query). I am using excel 2010
Pivot table example: the result i want is the "no of types" as my data only show "category" and "types".
Category
Types
No of Types
Fruits
Apple
Pear
Orange
3
Vegetable
Cabbage
1
I have 3 ranges of data which each have a quantity and a length. I want to create from these ranges a list of unique values with the total quantity required of each value (as shown). Inputs on any column may be blank but where there is a length, there will be an adjacent quantity to the left.
I'm using Excel 2010.
I have 29 excel files with some number of worksheets from 1 to 4. The name of the worksheets are the same in all the spreadsheets. Then I've a got a pivot table. I have to compare some data (3 columns) from the pivot table to the numbers from all these sheets from 29 excel files.
How to do it in a most efficient way?
So I have values in Column L3 to AH3 and I would like to use if condition to see which values are less than 10.
I also have other rows where this comparison needs to be done but for now I can't even get simple if condition to work.
this is what I am doing
For Each cell In Range("L3:AH3")
If cell.Value < 10 Then
ActiveCell.Font.Color = vbRed
End If
Next cell
I am not getting any error but for some reason only first cell condition is compared and not the other cells..
I have one excel 2010 workbook with 5 work sheets, each work sheet contains a list with first/last name(one column) and the company name, some have a 3rd column with their email address in each sheet represents each year starting at 2008 thru to 2013 i have to find out if the people that attended an event in 2008 also attended it in 2009/10/11/12/13 and if they didnt, put their name and company name onto a blank worksheet within the same workbook without using a macro, how can i do this?
View 2 Replies View RelatedI have column A and column B:
I will be inputting data into column B.
I need excel to check to see if the data I input into column B is an exact match to the data in column A.
If it is an exact match, then column B will remain blank.
If the data in column B is different, I need column B to show the following:
No match: <data>
Example I input in column B the following:
Column A Column B
1. Car Car
2. 4357 9999
3. fsd34d 4erd
4. 98dkf 98dkf
Spreadsheet should show:
Column A Column B
1. Car
2. 4357 No match: 9999
3. fsd34d No match: 4erd
4. 98dkf
(Cell 1 and 4 in column B are empty because they are exact matches to Column A cell 1 and 4)
My questions:
1) How does the excel formula need to be written for this to work?
2) Is there a way to set it so that when I do a mass copy to data into column B that the formula will not be overwritten and it will still check to see if the data I copy and pasted into that column matches the data next to it in column A?
I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.
My attempt is in cell D2 on sheet1.
I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'
I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'
See the attached image to make it clearer..
Excel2010
Excelhelp.jpg
I have a spreadsheet (Excel 2010). I want to fill categoryid in Sheet One based on values of Skill and State which are part of field in Sheet two.
Sheet One (Has Four Columns and I am looking for filling CategoryID based on Sheet Two
FirstName LastName Skill State CategoryID
John Edward Ballet California
Ed Catalino Tap London
Natasha Curtis Ballet Australia
Shen Watson Modern Kansas
Sheet Two
CategoryID CategoryDescription
1 Dancers/Ballet/United States/Alaska
2 Dancers/Ballet/United States/California
3 Dancers/Ballet/UnitedKingdom/Wales
4 Dancers/Ballet/UnitedKingdom/London
5 Dancers/Tap/United States/Alaska
6 Dancers/Tap/United States/California
7 Dancers/Tap/United Kingdom/Wales
8 Dancers/Tap/United Kingdom/London
9 Dancers/Ballet/Australia
10 Dancers/Modern/United States/Kansas
I need to make a excel costing model to calculate various products prices. I am using Excel 2010.
I have many products and do not need all of them displaying at the same time, therefor I inserted a Checkbox (ActiveX Control) that hides my columns that I do not need. I did that by inserting the following and it works fine :
[Code].....
The problem is I now need to lock certain cells so that they can remain fixed and the recipe cannot change.
Once locked my checkbox no longer works and I get the following error :
"Run-time error '1004': Unable to set hidden property of the range class"...
I'm trying to get the Median (and min/max later) of certain values on a different sheet. For example, I'm operating in one sheet and pulling the values from the sheet "Form Responses". I want a median for numbers when the values in column B are "Aransas" and the values in column D are Yes. The values are in Column F. My formula is:
=MEDIAN(IF('Form Responses'!B:B="Aransas",IF('Form Responses'!D:D="Yes",'Form Responses'!F:F,""),""))
It's giving me the median for ALL of the numbers in F and I can't figure out why.
Following that, I'll want a median for all the numbers in F when column B value is "Aransas" (in other words, dropping the Column D condition).
Excel 2010, Windows 7
I need to copy all sheets from a workbook to a another workbook (create a copy of the one I am using) and this is the cod I have:
Sub NewReport()
Dim Workbook1 As Workbook
Dim Workbook2 As Workbook
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With
[code].....
The problem I have is that the output only have the sheet1!
I am using Excel 2010