colA colB
ABC 123
ABC 456
ABC 789
ABC 789
ABC 789
I Need to write a VBA that will make a selection based on a couple conditions. example: if 123 is in the range of ABC and 789 is as well, i need it to select or change the color of the rows that 789 is present
I have a list of company names and I need to find the duplicates. Of course pivot table finds the exact matches, but how do I find duplicates when only 1 character is off. Like punctuation or "s" or a mistype of a single character?
Example:
Jo Blo inc
Jo Blo inc.
Jo Blo inc
Jo Blo icn
Joe Blo inc
Jo Blo inc
I have a list that looks sort of like this:
Last Name | First Name | Date | Service Day
Smith...........John....................Monday
Jones............Alex...................Thursday
etc..
This list goes on down for like 50 cells. Anyway, I'd like to make a new list based on Service Day so that I can organize the names under the service day so it should look something like this:
Monday | Tuesday | Wednesday | Thursday | etc..
Smith, John..............................Jones, Alex
etc...
I tried using the vlookup function but then it confused me when i also needed to match the names with their respective service days.
I have a question about making a variable selection.
In cell X25 is written "B9" and in cell Y25 is written "M39".
Now i want to make a selection by a macro from cell B9 to cell M39.
I have two worksheets. Each have a common value in two cells. An ID and a currency amount. One worksheet also has a payment date. I need a worksheet that combines the two, along with the payment date.
View 3 Replies View RelatedI have a column where a lot of the fields match. What I want to do is set up a formula where all of the matching fields are numbered (1,2,3,etc). For instance:.......
I want the numbering to read the matching items and count them (not a sum but rather just a numbering/ordering column)
This is what I would want it to look like:...........
Example:
Order Number from VendorOrder Number from SupplierTracking Number from Supplier12345123451ZABCD13456134561ZBCDE134561ZCDEF14567145671ZDEFG1456714567
In these three orders, the first order nubmer has a 1-to-1 relationship with a single tracking number. In the second, there are two different tracking numbers associated with one number from column A. In the third, there is only one tracking number but three different lines of the same order number that need to be updated with it.
The question is, is there a way to line up data fields head to head even when there are variable inputs like this?
I want to filter data using two sheets. i want only the names in a field that are listed on BOTH sheets. i have a two lists of names and i want only the names that are included in both sheets while still including the rest of their information.
View 4 Replies View RelatedThe order of column A can not change as I need to find the matching row of information and transfer that information
Not all values have a matching row.
Rows are match on PCN number but need all the values in the rows
I have attached a workbook with starting and desired end results
I have a large list like this:
A 1
B 2
C 3
D 4
E 5
I need only values pertaining to C and D. How do I sort the columns so it becomes like:
C 3
D 4
The rest I don't need.
I've been racking my puny brain for an hour trying to figure this out. Basically, my problem is with the last 2 invoices listed. If one invoice has "PO CLOSED" on it, I want all other invoices on that same PO to say the same thing. Surely I can do this with a formula or conditional formatting, but I cannot think of how.
PO #Invoice #DateInvoice TotalDept.ActivityElementPO AmountPO Amount RemainingPO Status0707955289651/12/09$812.9075105854035$2,960.28$59.380707955459806/15/09$667.2475105854035$2,960.28$59.380707956486827/7/09$812.9075105854035$2,960.28$59.380707955584669/29/09$607.8675105854035$2,960.28$59.380719750913336/18/09$947.7975105853305$5,000.00$3,117.210719750915917/17/09$935.0075105853305$5,000.00$3,117.21PO CLOSED
I need a formula. Probably Vlookup. I'm having a LOT of trouble trying to figure out how to "phrase" the formula so let me explain what it is I'm trying to do. If a cell in Sheet 1 Column AS and a cell in Sheet 2 Column B match exactly, I want the cell in Sheet 2 Column C that is in the same row as the matched cell in Sheet 2 Column B to be copied into Sheet 1 Column BB in the same row as the matched cell in Sheet 1 Column AS.
For example, if cells in S1 Column AS and S2 Column B both have the name "LOS ANGELES" then "KLAX" will be copied from cell in S2 Column C (in same row as "LOS ANGELES") to S1 Column BB (in same row as "LOS ANGELES").
CountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83
[Code]....
left side pivot created in vb 6.0 & right side pivot table created manually in excel.
i want to generated pivot table using vb 6.0 same as right side pivot.
Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),
[Code]....
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
programming 2 macros in a bank reconciliation sheet I am trying to create.
Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.
The following two situations can occur and which need to be covered with a macro.
Many to one matching
When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.
Partial cell matching
In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.
I have two tables, Table1 one has only customer codes in it, and I have Table2 with plenty of customer codes and those customer name, surname, age, location...
And I want to match and copy each of those customer codes in Table 1 all the information which is on Table2
Table 1:
50025
50026
50086
Table 2: Considering that name, age and location is each in separate cell
50025JohnSecond25Location 1
60085EmilyThird 20Location 2
45454Wilhelm Fourth35Location 3
10000RoseFifth 60Location 4
50086JohnySixth 45Location 5
65501JacobSeventh18Location 6
50026Jackie Eighth22Location 7
And the outcame should be that it finds the value from Table1 in Table2 and copy's information in Table 1 like this:
50025 JohnSecond25Location 1
50026 Jackie Eighth22Location 7
50086 Johny Sixth 45Location 5
I have two worksheet "ABX" and "ACX" from which I want to find the matching data for specific columns B, D and E only.
Matching data will be in sheet "Match" and vice versa.
Data are present dynamically.
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
View 1 Replies View RelatedI tried very hard to design a leave roster for user to mark their leave application. The criteria are as follows:
1. 4 applications per date
2. a region of worksheet (i.e. "A2 to H20) will be defined for users to "click" on the cells (within the defined region) to mark their application.
3. once a cell is clicked (i.e. marked), it cannot be altered.
4. each click will automatically increase the total by 1.
I got the following code to deal with the situation but user can still click on any cell outside the region (in fact I fail to define the region).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim m
ad = Mid(ActiveCell.Address, 2, 1)
m = Range(ad & 24).Value 'here a formula "CountA(A2,A20)" will be place in the cell (24, c).......................
I have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :-
If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.
I am working on a project where user has to select an item from a listbox, however there is a condition that if user select an item, other items of listbox should be disabled so that he/she can not select any other item.
Is there any way to do that as I tried
VB : Listbox.enabled = False
and
VB : Listbox.locket = True
in Listbox - Click/Change but no desirable results got.
I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.
e.g. In cell C115 I want to have the value BDS05.
I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14
If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.
I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).
I'm using the following code in a worksheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
col = ActiveCell.Column
Range("output") = ActiveCell.Offset(0, -(col - 4)).Value
End Sub
In case it's not obvious, the macro places the value in the active row and 4th column of the worksheet into the range "output".
The problem is, the worksheet is large and somewhat slow to recalculate. This macro forces a recalc on any selection change, but i only need it to run when the row selection changes, not the column.
I'm sure there's a straightforward way to reprogram this.
I'm trying to build an investment calculator. Tried searching "calculator" as keyword but return nothing similiar.
There are eight cell : [ A ][ B ][ C ][ D ][ E ][ F ][ G ][h]
[ H ] is price of the leverage
[A] is X balance
[b] is X lot size
[C] is X amount of money use to get x lot
[D] is X percentage of money use to get x lot
[E] is X point gain
[f] is X money gain
[G] is balance + money gain
Ignore the formulae for leverage to determine 1 lot price. Take 1 lot = 250.
What I'm looking for is when I enter any value at cell B, C or D, it will automatically calculate the value for either B, C or D cell.
Let say, the balance is 10000.
If I enter 2 at cell B. Then, it will calculate the value for cell C & D.
If I enter 750 at cell C, then it will calculate value for cell B & D.
If I enter 25 at cell D, it will calculate value for cell B & C.
how to use OR Selection in excel.
see attachment below for easier comprehension of what I need to be done. Note that I have around 20 subjects (2 in example) and about 15000 values per subject (5 per subject shown) Basically, I want matching rows to be aligned (see grey) and non-matching rows to be deleted, throughout the 20 or so subjects.
Exemple.xlsx
I have a cell (A1) that contains text.
That text will match a single value in a reference list (i.e. range of cells e.g. Z1:Z26).
When the match occurs (e.g. A1 = Z16), I want the format of the reference cell (fill color, text color) to apply to the cell of interest (A1).
The reference cells are manually formatted.
I know that I could create a long list of conditional format formulas instead of using the reference, but it seems that there must be some way to tell excel: if the cell value contents are equivalent, the formatting should be as well.
I am making a simple map in excel. I have my floor plan on sheet 1 and the location of product on the floor on sheet 2.
Currently I have some formulas on sheet 1 that link to some sales data on sheet 2, but what I would really like to be able to do is use the search function on page 1 and it give me the location of the products in a given area.
The data is broken up into group location on the second sheet.
If the selection (or any part of the selection) = bla bla bla
I have four columns containing about 200 addresses.
A Names
B Streets
C Cities
D Phone no.
If I write a name in D1 I want to get the corresponding street, city and phone no. in E1, F1 and G1.
I also want to copy the formula downwards let's say ten times so I can make a list.
If I for example just want to make a list of three names (D1-D3) I want the rest of the fields to stay blank.
I've created an Add-in (.xlam file) in Excel 2007 and installed and activated it (it shows up under "Active Application Add-ins" on the Add-in menu for new workbooks).
I can see the macros and code when I go to the VBA editor, but the macros don't show up in the regular macro list.
I know that I can write code for the new workbook that will reference the Add-in code, but I want other users to be able to install the add-in and run the macros in it right away without any knowledge of VBA. Is there a way to make this possible?