I'm interested in comparing the 2 columns of data as attached in the SampleData file, for eg. First customer bought 5 items and customer 2 bought 3 items. I'd like to show the difference which customer 1 bought and customer 2 did not buy. Will need to use that via VBA And also for loop if possible!
SampleData.xlsx
I'm looking at trying to view 1 large keyword phrase list of about 40,000 phrases to another large list.
All I want is a macro, I can assign a button to. Click it and in a new sheet it shows only the rows of data that are different.
I have tried googling it.
I found 1 that looked very good (Not that I really know anything about coding, but I couldnt get it to work.
I'm using excel 2007.
As a note the link was here.
[url]
I might as well post the code,, but I just couldn't get it to work, shame, because it sounds like it could be what I was after, I'll post it now, but if anyone can help me with this that would be great
Sub CompareWorksheetRanges(rng1 As Range, rng2 As Range)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
If rng1 Is Nothing Or rng2 Is Nothing Then Exit Sub
If rng1.Areas.Count > 1 Or rng2.Areas.Count > 1 Then
MsgBox "Can't compare multiple selections!", _
vbExclamation, "Compare Worksheet Ranges"
Exit Sub
I have a spreadsheet which has a number of columns populated with a day number and below each day number there are some numbers. What I would like to do is to compare the values for Day1 with Day2, Day 2 with Day 3 etc. Is there a smart way to do this using VBA? I am enclosing an example to show what I would like the macro to do.
View 8 Replies View Relatedi am comparing 2 inventories, they contain the same information. I have included these on 1 sheet
Column A: Part numnber Inventory 1
Column B: Stock number Inventory 1
Column C: Quantity Inventory 1
Column E: Part numnber Inventory 2
Column F: Stock number Inventory 2
Column G: Quantity Inventory 2
I need a formula that says: If column B = column F, return the difference, if any, of Coulmn C - Column G to Column H
The data is not going to be inline, so B1 is not necessarily going to = F1
I have data entry in a spreadsheet which shows minutes, seconds and thousandths of a second - example looks as follows:
12:48:589 or 04:21:998. I would like to be able show the time difference between two data entries, so for example:
09:57:145 and 08:12:055 would give a difference of 1:45:090
12:07:985 and 18:59:788 would give a difference of 6:51:803
To be honest, I even struggled to work out the values on paper. Is this even possible? If so, can you let me know the number format I should be using as well as the formula or even better, post an excel sheet with the example.
I have two columns of dates. I want to see if there are duplicate dates and highlight them in the first column. What is the easiest way to do this?
View 9 Replies View RelatedI have two columns (A and B) with the same data. Column A is missing a value that is in column B.
Column A has 11330 rows whereas Column B has 11331 rows.
I am trying to do a comparison on the two columns to determine which value is missing from Column A.
I am using Excel 2010.
how to create a macro of some sort that will look at a list of data in cloumn A and in Column B then tell me the values of data that is in A but not in B then also tell what is in B that isnt in A. I understand that 2 vlookups will prob do this but i would like it more user friendly so the way i would like it...........
is for me to paste a list of data from 1st source to column A
then, a list of data from 2nd source to column B
then click a button that would do a lookup from column A against B (and B against A)
and return the values on sheet 2
that way... users could look on sheet 2 and anything in column A would be data that was in column A in sheet 1 but not in B and the data in cloumn B (sheet 2) would be the data that was in column B on sheet 1 but not in A.
if the cells in column E AND column F are blank, then column G will be blank also (I dont want 0 showing), if not show me the difference between col F and col G.
I put in the following:-
=IF((E2="")AND(F2=""),"",E2-F2)
However, Excel informs me this is wrong and corrected it as
=IF((E2="")*AND(F2=""),"",E2-F2)
I guess my question is why do I need to put in "*" for this to work?
How to show via a message box the difference between rows 2 and 3 on a worksheet (row 1 is headers).
In the message box per the attached workbook, i should only see something like the following....
Drawing Rev was A is now B
Planner was Joe is now Fred
The attached example is abbreviated, the actual data will span multiple columns (approx. 30 - 40)
I've two columns of numbers in Col J and Col P how can I make cell P2 characters that are not in J2 show the difference by making them bold
J2 = 00342 P2 = 0034211
J3 = 00344 P3 = 003443
J4 = 233 P4 = 23377
Till last row with data in the sheet.
I have 2 cells in which time is given in format hh:mm:ss , I have differentiated both whatever difference is their between them it get showed in different cell , but my cell is not showing exact time difference its showing up as ######.
View 6 Replies View RelatedI"m looking for a formula that searches for sales from a sheet (that already has the sales ranked) and populates that into another sheet. But the 3 cells above/below would have the difference in sales.
Rank
1.
2.
3. Rank3-MySales
4. Rank4-MySales
5. Rank5-MySales
6. MySales
7. Rank7-MySales
8. Rank8-MySales
9. Rank9-MySales
10.
So something like this:
Rank
1.
2.
3. 3500
4. 2700
5. 950
6. $5000
7. -1200
8. -1550
9. -2400
10.
Have an Excel 2010 spreadsheet with 902 email addresses in one column, and 927 email addresses in other column, sorted in alphabetical order.
Overview: Organization database has 902 Members (some have 2 email addresses subscribed to listserv) and Organizational Listserv which has all 927 (Difference is numbers are some members have 2 email addresses subscribed) BUT 902 of emails should be the same
Objective to compare Listserv Members 927 with Database Members 902. I am trying to isolate the email addresses which do NOT exist in Listserv Column, or who do not exist in Database Column for Audit purposes. All Members from the Database column should be subscribed or entered into the Listserv column.
If it's a perfect world the 902 email addresses will all be exactly the same as 902 out of the 927 Listserv email addresses.
Trying to highlight or sort and put identical email addresses on same line or using Conditionally formatting? Highlight Cell Rules/Duplicate cell rules Even when same email addresses are on the same row conditional formatting does not recognize them.
I have a table with row headers (in column A) and column headers (in Row 1). Throughout the table there are 1's and blanks depending if the column is applicable to a particular row or not.
I want to write a VBA macro to do the following:
1. Do a Vlookup of column A for a particular user input text string.
2. Only show the row that matches the text string.
3. Only show the columns where there is a 1 associated with the selected row.
Then once that is done I need a separate macro to return the sheet to show all the rows and columns.
I'm trying to get an excel custom number format to show the difference in two dates as 0 years and 0 months...for e.g.
Cell a1 = commencement date = 1/1/05
cell a2 = expiration date = 12/31/10
Then cell a3 = Lease Term = 6 years 0 months.
I am trying to do a multiple compare.
First I have a table that has these headings with data..
Name, Level, Req1, Req2, Req3, Req4
Bob, 2, 423, 506, 649, 798
Fred, 4, 601, 799, 1023, 1547
In another section I have..
name Drop down, level drop down. with the 4 Req.
Basically, when you select Fred and 4, I want to to display 601, 799, 1023, and 1547 in the columns to the left of where you do the selection. (which is not in the same place as that table at the top of this question.
How can I compare the data of two columns, and if the same, to show me the number from a third column? I upload a quick sample
View 4 Replies View RelatedI have a Pivot Table with fields for months and weeks. I also have a "Show Values as % Difference Field" that shows monthly or weekly % change. When I collapse the fields so that it goes from weekly to monthly (or vice versa), I have to manually change each Show Values As % Difference column. Is there a way to do this automatically or quickly?
View 1 Replies View RelatedSheet1 is Main Sheet which never will be amended and these numbers will be a source for pulling mathematical functions
See attached :
Sheet1.png
Don't be bothered about cell colors etc. They are for my reference
Sheet2
Will contain same Problem Ticket numbers but different (or same) values with number of 'Linked Incidents'
See screenshot
Sheet2.png
Based on Problem ID 10248
Main sheet shows 92 Linked Incidents
Value on Sheet two brings 93
If A5 Sheet2 value can be found in ColumnA Sheet1, look for a number in ColumnC, same ROW and compare it to C7 in Sheet2 ?
Bring up difference value, which in this case would be one.
Basically I have a form where the list can expand and contract so there will always be varying row lengths.
What I would like to see is a drop down list that shows a) the actual populated field and b) a description of the field when the drop down list is present.
I believe this can be done with a combo box however that would be a lot of work.
I've got a problem with organizing my data. I've performed a study with several participants, each of which does several trials with 8 conditions (1,2,3,4,5,6,7, or 8), with each participant doing a condition more than once. I also have a separate column telling me whether they responded correctly or incorrectly (with a 1 or a 0).
I need to find a way to produce a new column to identify whether they got each condition (of the conditions 1,2,3,4,5,6,7, or 8) correct separately, i.e one column for responses to condition 1, one for condition 2 and so on....
It would also be useful if there was a way that once this is done I could summarize their accuracy of responses to each condition.
I've attached an example of my data. excel problem example.xlsx
I have an excel 2003 sheet that collects data from Infopath forms. The forms are to record students who have broken school rules, when, where, repercussions etc. One column shows their class and there is a separate column for each rule broken.
I want to create another sheet to show each class down the rows and the columns to show each school rule. Therefore, each cell would show the number of each particular rule broken for each particular class. I have tried to do countif and sumproduct (if on sheet 1, column B the class is KA and on sheet 1, column M, the rule is bullying = how many times this has occurred).
I have two columns of items that need to be compaired. What I need is the items in column B that do not match any of the items in column A need to be put in Column C. How can I do this? The Data in Column A can not be re arranged. Column B can be sorted.
View 9 Replies View RelatedI have a spreadsheet and I'm comparing data from a room booking system and a spot check on the room to see whether it's actually used. I have a column which I want to show whether the information agrees.
column G - I have a group size recorded that should be in a room.
column H - I have "yes" or "no" as to whether there was anyone in the room when it was checked
column I - I have the size of the actual group in the room.
Is it possible to create a formula that will fill in column J with the answer yes or no depending on whether it agrees.
It doesn't matter whether the groups sizes match, it's just a case of yes, someone was booked into the room and there was someone in when it was checked
OR
No, there was a booking supposed to be in the room but there was no one in it when checked
OR
There was no booking but there was someone in the room when checked.
With a set of data in colums by quarter and department names in by row I need to answer build a formula that answers simple questions like "what department had the fewest sick days per quarter?" and which department had the most sick days per quarter?"
View 4 Replies View RelatedI have a database with 65536 rows and A-BZ columns
What I am wanting to do is, on another worksheet be able to select identifying data from one or more columns from the database but have it display the entire row.
For example
WCP28 SX
WCP28 TX
WCT36 SX
WCT36 TX
In this example I would like to, on another worksheet, be able to create a drop down where I could select WCP28 and it would show me both the TX and SX row. Or I would like to be able to select WCP28 SX and WCT36 SX and see the rows next to each other. If it were for me I would just use the auto filter and manually compare but this is for somebody even less inclined than I am.
I would like to insert a function in spreadsheet A that looks up data in
spreadsheet B based on _two_ comparison values.
The two comparison values in spreadsheet A would always be in the same row
and the corresponding match in spreadsheet B would also have to be in the
same row.
For instance, if the comparison values in cells A2 and B2 of spreasheet A
match with the comparison values in cells A30:B30 of spreadsheet B, then
show the value that is in column C30 of spreasheet B.
Excel macros. I'm trying to figure out a script that will look at alphanumeric numbers in column A (A2 --> for example) and compare it to the number directly below it and above it. If it matches either, do nothing, but if it is different, delete the entire row that the different number resides on.
An example of the data would be:
A
12A34A5
12A34A5
67F86F3 - when it encounters these rows I need to delete.
23G45G6
23G45G6
The sheet is a couple thousand lines in length, so I can't deal with it manually or even with sorts, due to the other data in the other columns.
I am trying to figure out a way in excel to graph some data I have in a new, sophisticated type of chart. My data is new square foot sales divided by new square feet (for a retail store). It is a dollar amount. I also have this data as a percentage change year over year. If I want to compare two stores with different data -- what would be the best way to present it on a chart. Is there a way to show this on a 3-D plane so that both companies have the data in the same chart up against each other?
View 2 Replies View Related