There are 2 columns (A, B). So now I want to check if there are same cell values within both columns.
Say for example A1.value= "red", so I want to check if "red" exists in anywhere column B. And if it exists both cells needs to be highlighted. And there are tons of rows in columns A, B
Any macro or conditional formatting?
the support this board has given me as I learn VBA. I have three columns - Q, R, and S. I only need to see columns R and S if the cell values don't equal those in column Q or each other. So if I have cell values like the ones listed in the example below, then I don't need to see columns R and S.
Q Header
R Header
S Header
50
50
50
[Code]....
I have a standard data import that populates data in columns A through DC. I am trying to delete those columns from BX through DA where the value in row 2 equals 0.
View 4 Replies View RelatedI have an array that is 1 row high by 16 columns wide.
Each cell may contain a positive value, or a zero.
I need a formula to find the "Minimum value that is greater than zero" in
columns 1,3,5,7,9,11,13 and 15.
=MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
need the minimum value that is greater than zero.
If I use nested IF functions to exclude zeroes I run foul of the max of 7
allowed.
I am looking for formula , single cell two numerical values , multiply in different columns
example :
Cell (A1) contain: =350,23+25,59 (two values in a single cell)
request:
in (B2) only first value so 350,23 multiply (x) by fixed number &in (C2) only second value so 25,59 multiply (x) by fixed number
I got 3 columns of cell values as follows:
Demo 1
COL A COL B COL C COL D
_ 12 14 16
_ 32 12 18
_ EA LW RA
Demo 2
COL A COL B COL C COL D
12 12 14 16
32 32 12 18
EA EA LW RA
14
12
LW
16
18
RA
Can data of the 3 columns be copies (by vba code) to the blank col A in the exact sequence as shown in Demo 2?
Im trying to come up with an automated way of splitting concatenated information but putting each concatenated value in a seperate row.( i have columns of data that need to stay with each concatenated value) Eg
Concatenated comma delimited data
Cust Name Cust Identifier Locations
L Kim 543545 California,Chicago,Florida
S David 31434 Maryland,Vermont,Maine
D Bryant 572575 Texas,Oaklahoma,Nebraska
i need to to get a result that looks like the below example im currently just doing text to column filterting and copying all the columns over and stacking them on top of each other. i need to find a way to automate this process but i cant seem to think of one
Customer name Customer Identifier Location
L Kim 543545 California
L Kim 543545 Chicago
L Kim 543545 Florida
S David 31434 Maryland
S David 31434 Vermont
S David 31434 Maine
D Bryant 572575 Texas
D Bryant 572575 Oaklahoma
D Bryant 572575 Nebraska
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 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 would like to know if there is a vba code doing this I have eg 100 columns starting from the column B. At the first 3 rows there are some data. Now im using the data from the rows 2 and 3. The row 2 has column numbers (TEXT formatted) from the user. The numbers starts from the 01 and goes on. But it mights be more columns numbered with the 01 or 02 or 03 or.. The row 3 has consecutive numbers and stars. The numbers start from the 1 and stop when the row 2 changes the number. The star appears when the user insert a new column (to the following example are the columns J, K, L &M )
B----C-----D-----E----F-----G-----H-----I------J-----K------L-----M---
null null ....
01---01---01----02---02----03----03----03----03----04-----04----05---
1----2-----3-----1----2-----1------2-----3-----4------*-----*----*----
I want a vba code that it has to look for the stars and then to change them to consecutive numbers depending on the above row. So, the previous exmample it would be after running the code like this.
B----C-----D-----E----F-----G-----H-----I------J-----K------L-----M---
null null ....
01---01---01----02---02----03----03----03----03----04-----04----05---
1----2-----3-----1----2-----1------2-----3-----4-----1------2-----1----
I have one column of data. there are currently ~10k rows, but this will increase over time. each row has either a single value (example: pepsi) or a comma separated value (google,samsung) with up to 6 commas. instead of the rows looking like this:
pepsi
google,samsung
coca-cola
I want them to look like this in one column (preferably via a formula):
pepsi
google
samsung
coca-cola
I would like the above output to be a unique, alphabetized list.
write a script to match a cell value then copy and paste the matching row across two worksheets - "Master List" and "Demographics".
These two worksheets have the same cell values in Column A, the key difference is the Column A cell order in the second worksheet "Demographics". This Column A cell order is set arbitrarily by another system and will vary over time.
I would like to match the rows, then paste the three columns from the second worksheet into the first worksheet.
For example:
"Demographics" Worksheet Cell A2 = "Master List" Worksheet A45 - then copy cells A2, B2 and C2 and paste into A45, B45, C45 respectively.
"Demographics" Worksheet Cell A49 = "Master List" Worksheet A12 - then copy cells A49, B49 and C49 and paste into A12, B12, C12 respectively.
I have created a sample spreadsheet here; - List Schema.xlsx
I request you to write a code for me to fill the cell values as "Not Applicable" in Column "AZ", if the "B" Column cell values = "Justified", "Approved LSAR" & "Approved SDAR".
I have attached the work book of what I am trying to accomplish.
Here's an example of what i'm working with,...
Range is C40:D48
it should compare and see if product, model for same id is the same but brand is different and the quantity of one or more of those brands=0 then the result (In column F)should combine the brands. check the attached image for more details.
View 5 Replies View RelatedWhen Cells "Month Input"(C21) and "Network Input"(E21) are populated with one of the values from columns C(C2:C14) and E(E2:E14) respectively then the Destination Cell should be populated with the corresponding value from Column F(F2:F14) into Destination Cell H20.
View 11 Replies View RelatedI am trying to utilise a spreadsheet i set up to monitor annual leave to work on desk allocation.
The problem is i want the bottom of a column to sum or count based on the entry above it as long as another column had a certain value.
To explain.
Column C says if a person is an advisor and has the value "Y" if there are and if not it will be blank.
Now there are another 30 odd columns after that and i need a total at the bottom of each seperatly to say if a row above has an "a" in it & the C column has a Y
So i want to total all the entries in the E column that have and A entry & a Y entry in the C column.
I will then continue this over the F ,g etc
I am sre it is much easier than i have explained it
I am trying to count how many times a certain value appears in a column. For example how mnay times A81001 appears in column A and compare with column B. I suppose it's Count function?
column A column B
A81001A81001
A81001A81001
A81002A81002
A81002A81002
A81002A81002
A81002A81002
A81002A81002
A81003A81003
A81003A81003
A81003A81004
A81004A81004
A81004A81004
A81004A81004
A81004A81005
I need creating a formula that matches the values between two separate columns with results from that matching in the third and fourth columns.
Example: Column A contains the first set of values and column B contains the second set of values. The result in column C would be all the values that are in both columns A and B and the result in column D are all the values that are not found in both columns A and B.
Column AColumn BColumn CColumn D
122123123122
123133322133
231221323221
311322231
322323311
323333333
See attached spreadsheet : match_values.xlsx
I need a formula that matches a number on Sheet1 with a number on Sheet2. Sheet1 contains 7 columns of numbers. Each column corresponds with the same column on Sheet2 if the number on Sheet1 column 1 can be found in Sheet2 column 1 then the result is True. If the number is not found in Sheet2 column 1 then the result is False. This same criteria would apply to columns 2 through 7 as well.
The formula would start by selecting a number in Sheet1 that corresponds with the formula column and then scan the entirety of the matching column on Sheet2 for that number. If it is found in that column the result is TRUE.
Sheet1 H3 corresponds to Sheet1 A3 scans all of Sheet2 column B for the value in A3 if it's in Sheet2 column B the result in H3 is True if it's not then the result in H3 is False.
EXAMPLES:
Sheet1: column heading 1 - (A3) = 2223333333
Sheet2: column heading 1A - (B72) = 2223333333
Result H3 = TRUE
Sheet1: column heading 1 - (B3) = 222333333314
Sheet2: column heading 1A - (C67) = 222333333314
Result I3 = TRUE
Sheet1: column heading 1 - (C3) = 222333333332
Sheet2: column heading 1A - (D61) = 222333333332
Result J3 = TRUE
Sheet1: column heading 1 - (D3) = 222333333321
Sheet2: column heading 1A - (E62) = 222333333321
Result K3 = TRUE
Sheet1: column heading 1 - (E3) = 222333333331
Sheet2: column heading 1A - (F65) = 222333333331
Result L3 = TRUE
Sheet1: column heading 1 - (F3) = 222333333351
Sheet2: column heading 1A - (G70) = 222333333351
Result M3 = TRUE
Sheet1: column heading 1 - (G3) = 222333333352
Sheet2: column heading 1A - (H69) = 222333333352
Result N3 = TRUE
I've attached a workbook with examples : match_numbers.xlsx
I am creating a payroll spreadsheet that will look up tax deductions from a spreadsheet I pulled from the IRS website. The first two columns represent a range of values the employee's pay would fall between. The first column is "paid at least" and the second column is "but paid less than".
Once I locate which row the employee's wages fall between, I would then need to match their number of withholdings with the corresponding column. Columns 3, 4 and 5 have headings to represent 0, 1 or 2 withholdings.
The value I need returned would be the intersection of wages paid and withholdings. For my spreadsheet example, if the employee's wages are 1,023 and they have 1 withholding, I need the formula to return 147. I was able to find a vlookup/match formula but it worked only without a range of wages paid.
For example, if the employee was paid exactly 1,030 and had 1 withholding, it would vlookup/match to return 147. Obviously, this won't work with me needing to find the correct row based on where their wage falls in the ranges.
I've attached a picture of the spreadsheet example below. payroll example.jpg
I am having a hard time with a copying values, it is best if I make an example. The idea is: if A1 is "Toyota" and there is some other text also in B1, B2 and B3 then A1,A2 and A3 are "Toyota"
Book1.xls
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 View RelatedIf I am creating a vlookup forumulae, and I want to return values from several columns, I have been manually changing the column it's returning the value from when I drag the formula across the columns. This is ok for a few columns.
eg =VLOOKUP($D2,$AB:$AJ,1,FALSE), dragged across columns, and then manually edited in each column to read
=VLOOKUP($D2,$AB:$AJ,1,FALSE), =VLOOKUP($D2,$AB:$AJ,2,FALSE), =VLOOKUP($D2,$AB:$AJ,3,FALSE), =VLOOKUP($D2,$AB:$AJ,4,FALSE)
etc
The value doesn't increase when you drag it.
is there a short cut for doing this automatically?
i have information across three columns the first has user-names in each row the whole way down, the second has between 1-7 activity codes (when not eacher user will use), the third has the times they have been on these codes.
what im trying to do is match the name, code and get the time to be displayed in a fix table, as the reported information is not always in the same structer
eg
user1 code 1 0:02:00
user1
user1
user2 code 3 0:05:00
user2 code 6 0:20:00
user2
now i've got it in my head that sumproduct iwll be the best way to get it, but i cant seam to get the third array to work properly, and always comes up with either value or NA