I have 6 columns containing Agency IDs of different years. I would like to know which agencies appear in all the years. That is which agencies were funded for all the years. How can I do this? perhaps Vlookup?
View 11 Replies
I have a fairly large timecourse dataset and I need to find all common values within all 3 columns. Also, when I find these 'common values' is there a speedy way to retrieve data in the same row that is associated with these values, instead of going back one-by-one and copying and pasting beside the value that the function has returned?
View 10 Replies View RelatedI got 2 excel pivot tables with 2 columns.
Table 1 (Sheet 1) -- Column A and B - input
3
45
4
12
[Code].....
I want to compare both tables, finding the same Number on Column 1 for each table, and sum the values of column 2.
I have a workbook with two sheets, and I am having difficulty in finding how to match up the data.
Sheet 1 (named "TS") consists of several columns, but it's always a series of date columns followed by multiple site numbers.
Sheet 2 (named "Dates") consists of just two columns, a site ID and a Date column.
I am having difficulty in finding how to lookup/match the date that goes with the store number, as the information in Sheet 1 (TS) is laid out in a horizontal fashion.
So, for example:
Sites: 14352, 14425, 11661, 404, 8142, 3571 should all have the date of 5/17/2014.
As such, on Sheet 2 (Dates), whenever those site IDs are encountered, the date of 5/17/2014 would need to be applied in col B.
Similarly, on Sheet 2 (Dates), for site IDs 1201, 3613, 9316, 9735, 13211, 7640, the date of 6/21/2014 would need to be applied in col B.
Here is a link to a sample workbook: [URL] ........
I have gone through various posts but am unable to get the answer. What I'm trying to do is find if a value (text or number) in Col. A exists anywhere in Col C. and if it does, find the corresponding entry in Col. D and paste it back against the value of Col. A in Col. B. To explain better I have attached a sample excel file Ex1. In the file, the entry "sun" in Col A is present in Col C and the corresponding value in Col D is 24 which should be pasted back against "sun" in Col B.
View 3 Replies View RelatedI have two list of names, mostly duplicates. List in column B has about 30 more names than the list in Column A and I need to identify which names are in column B that are not in column A. Names are in exact same format since they were pulled from the same data base.
View 1 Replies View Relatedvba code to find multi values in a Sheet With 6 columns values like :1,3, 12,16, 20 ,31, 36, 44, 46,
View 2 Replies View RelatedIs there a function to compare 2 columns to find all of the values from one column that are not in the other column? Basically my data looks like this:
A........................B.................C
procedure1...........................225-3
........................ 25-1...........22-2
.........................33 .............457
procedure2...........................33-55
.......................35-12...........25-6
.......................12-8.............25-1
procedure3...........................33-89
.....
I have another list of data (just the numbers like columnB) that I want to put in column C and see what is in column C that isn't in column B. The numbers will be arranged in a different order, and there will be data in column B that isn't in column C. What I need to do is find out what is in column C that isn't in column B so that I can go back and define what procedure they fit into (that is a seperate process).
I have 2 spreadsheets, I need to collect the data onto 'figures 07' from column D on 'spreadsheet 07' by date, based on the name matching. ie cell B4 on 'figures 07' should be 166 and cell C4 should be 72. I've tried IF and LOOKUP but they don't give me the correct answer Auto Merged Post;Here is the second spreadsheet, it wouldn't let me attach both of them on my first message
View 4 Replies View Relatedwriting a macro to find the max value in each column and delete all the data points that come after it (or preferably: delete all the data points that come 2 rows down after the max, if possible). There will be many columns of data where the max will come at different positions in the column.
Instead of deleting post-max values, it would also be acceptable to just copy values from the beginning to the max to the same column in a new worksheet.
I have VBA code to perform some actions on data in excel file and then convert all that data into semi-colon separated CSV/text file (code below).
Now, all I want is to add VBA code in the existing macro to find a column header (say, "Application date") and then convert all the dates into YYYY-MM-DD format. The original values in this column don't have a fixed date format.
Code:
Public Sub ExportToCsvFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendDataOnExistingFile As Boolean)
Dim WholeLine As String Dim FNum As Integer
Dim RowNdx As Long Dim ColNdx As Integer
[code]....
Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
The error happens at this point: ...
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678
In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
I have data similar as shown in the following:
A1A11
B2AA1122
C3B22
D4C33
D44
The idea is to add compare the cells of the first column with the third column. Where same letters/words exist, the corresponding value of the first column should be added to the second column (where no letter exists equally, the space remains empty), so it will look like this
A111
AA1122
B222
C333
D444
the third column always will have at least the same letters as the first column, but new letters/entries can occur.
compare some list of values in order to fiind the common values.
View 1 Replies View RelatedTrying to find the sum of all cells in the array described in the formula that are equal to the values inside the quotations. I used this exact (as far as I can tell) formula to find the sum of values that were NOT equal to my quoted values and it worked just fine. Any ideas why formula 'A' will not work but formula 'B' does work? I have a feeling I'm missing something simple here!
Formula A - Does not work:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000="REJECTED"),--('Master Lead Sheet'!$N$2:$N$10000="CONDITIONED"),--('Master Lead Sheet'!$N$2:$N$10000="APPROVED"))
Formla B - Works:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000"No Answer"),--('Master Lead Sheet'!$N$2:$N$10000"Disconnected"),--('Master Lead Sheet'!$N$2:$N$10000"Wrong Number"),--('Master Lead Sheet'!$N$2:$N$10000"EMAILED"),--('Master Lead Sheet'!$N$2:$N$10000"needs to be emailed"),--('Master Lead Sheet'!$N$2:$N$10000"Refund"),--('Master Lead Sheet'!$N$2:$N$10000"REFUNDED"))
I have data that looks like this:
day# id amount
1 56575 0
1 56675 0
1 56680 0
1 56683 0
1 56681 0
1 51810 0..............
How do you write an excel macro that looks at the number in the first column (day #) and finds all the duplicate id#s in the second column that are in day 1and adds the amounts together in the 3rd column then writes the first column number (day#), second column number(id#) and the third column (sum of the amounts of duplicate Id#) to an new worksheet. Then the macro would loop through day #2 and do the same thing. Notice that the values in the id column are unique in this data set below this is how I would like the data to look. I have accomplished this in a pivot table but my problem is I need a cvs file to export the final data into an external database which is why I need a macro.....
I have set of data in multiple range ,need to fill the and replace the old values depends upon two column values (AH & AL)
IF Active Calls is "TATA" In AH:AH, and IF Action Onwer Col is "Blank",in AL:AL
Then Fill the Blank cells by Values "SVC" in the col Action Owner,Then Replace Old values by "Updates Awaited" in Status Col(AM:AM)
Find the attachment & basic code take this code for this task
[Code] ....
toggle-2.xlsb‎
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
I would like to align the matching values in columns A and B but I want the values that correspond to b in c,d and e to go with the column b value.
C D and E don't necessarily have a value in there but if it does it needs to move with B
I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.
What would be the best way to do so?
Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.
I have attached an example file that represents how the data is stored and the output I would like (Unique Count).
I have different reports, some have fifty transactions, others have thousands. My goal is to: Insert a new row every time the values in the "Account" column meet a certain criteria, AND THEN add the totals for the Debit and Credit Columns.
Let's say I start with a table that looks like this:
A
B
C
D
Dept
E
Account
T
F
F2
G
Debit
Credit
Total
33010
[Code] ....
I want to group the first four rows because Accounts 33010 and 33015 are in the same department. Same with 50050 and 500060. I want to then insert a row below the last row with "33015" as its Account #. And add the values for Debit and Credit. It'd look like this:
A
B
C
D
Dept
E
Account
[Code] .........
Honestly, I have tried everything. Running a Macros with Relative reference does not cut it.
I have a budget spreadsheet as follows:
Category, Sub-Category, Amount
Food, Lunch, 10
Food, Dinner, 20
Food, Lunch, 15
Food, Dinner, 30
I need to track how much was actually spent on various categories and sub-categories, as follows:
Category, Sub-Category, Budget Amount, Actual Amount
Food, Lunch, 50, 25
Food, Dinner, 90, 50
Essentially, the actual amount contains a set of formula, that sums up the spending sheet (the one on top) based the category and sub-category on the budget sheet (the one on the bottom). Tried to use SUMIF but it seems to work on a single column.
i have a matrix and want find min & max this matrix. know must use For and IF for example for MIN: i want "a" compare "b" and if "a" < "b" then "a" is local optimum else b is local optimum then "c" compare "a" if "a" < "c" then "a" is local optimum else c replace a and c is local optimum &... until find Global Min
but i could not coding this in VBA EXCEL!!
i do not know number of my columns user will define it so for example i can't use Range("A1")
My problem became greater because at start user will define a number for every column(1 or 2)
and if column value was 1 for example find Max column and put it in cell(1000,1000) then find min column and put it in cell(1001,1000) else i.e if column value was 2 find min column and put it in cell(1000,1000) then find Max column and put it in cell(1001,1000)
numerical example:
column 1: 12 13 15 14 16
column 2: 8 10 9 2 3
and column value for column1 equal "1" and for column 2 equal "2"
so for column1 in cell(1000,1000) will write "16" and in cell(1001,1000) will write "12"
but in column2 because column value equal 2 will have: in cell(1000,1000)= 2 and in cell(1001,1000)=10
I have x coordinates in column 1 with coresponding y coordinates in column 2. From my data of x,y coordinates I want to find points with similar y values. In my data I have defined groups of numbers, i.e. I have a set of numbers with y values around 30 (+-10), then a new group with y values around 60 (+-10), and so on... Sometimes the groups are not totaly separeted, there could be a few points with y values between 30 and 60. These points can be grouped with the closest group of coordinates (30 or 60). Each group of x,y coordinates could be copypasted in the columns to the right (columns 3 and up).
So what I want to do is find a group of coordinates. This group will have at least 40 individual points +-10 from a group mean. The coordinates need to be sorted out from the data and put in seperate columns.
I have two lists of email addresses in columns and I need to find the matches between columns and have those addresses returned to perhaps a third column.
View 2 Replies View RelatedI have a number in column “A” and I want to match them with column “D“, the number in column “C” and “D” is true value. find attached sample sheet for more detail.
View 6 Replies View RelatedI have a 3 column table that goes:
FROM: /// TO: /// Value
0 /// 25,000 /// x
25,001 /// 50,000 /// y
50,001 /// 75,000 /// z
etc etc.
I then have another table ( 2 columns) with the first column being inputed values from another worksheet. This inputted number will fall between one of the ranges. The second column needs to have the appropriate VALUE from the above, 3 column table.
How do I go about making a Vlookup that can find ranges between 2 columns?
So if I have a value of 30,000, it will return "y".
I have Dates as column headings, People going down across rows, and their corresponding budgeted hours:
Person Last Day 1/1 1/8 1/15
Bob ? 45 45 0
John ? 45 0 0
I need a formula in the "Last Day" column that can pick up when a person rolls off the project, i.e. has a value of "0" or "-".
I need this because I have a spreadsheet with thousands of resources and don't want to comb through, manually looking for their last day on the project.
