I need to build a formular in a cell that uses various columns in it's row such as: =A2+A4+A17+A24. In the past I've used simple number references to the columns (a is 1, b is 2,.....) I can't do tht though if I'm using a macro to automate the creation of a formula, right?
Is there a property I'm missing that will return the letter reference to the column instead of just the number as Activecell.column does?
My alternative is to write my own code to do this, but you know the bit about not reinventing the wheel....
I want to do, is search column A for claim numbers that match. When I do have a matching claim number, I want to concatenate the original cells ownership field with the said matching cells ownership field (or move into a column in the same row, I can always concatenate later). Once that is complete, I want to delete the row I took the information out of.
I want to join this data in ArcGIS, but as of right now, it's not a 1-to-1 relationship, so only a relate works. That doesn't help me as I want to display claims by ownership, and this can vary per claim. Company A may have 100% on one claim, and then split another claim 50% with Company B.
This causes a double entry on the claim field in this current spreadsheet I have, which requires me to clean it up by making multiple columns of ownership vs. an additional row for shared ownership.
My problem:
Column A Column B 1235555 Company A (50%) 1235555 Company B (50%) 1235556 Company A (100%) 1235557 Company A (33%) 1235557 Company B (33%) 1235557 Company C (33%)
What I would like to see
Column A Column B Column C Column D 1235555 Company A (50%) Company B (50%) 1235556 Company A (100%) 1235557 Company A (33%) Company B (33%) Company C (33%)
1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else Row 2 is my header, so cell A2 says "ID" Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...
I need to populate the Rep Names looking up 3 Criteria (Client Id,Curr Cov Id,Dom Buy Grp Id) from the Table 1, either one matches the Rep Names has to populate.
The data has to be pasted on WIP_Sheet in different columns.
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D. e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
I would like to check/search if (FileName As String) is still exists in its directory. --Why as string: because I have a hidden kollom where the links to the files are in txt format.--
Here is a sample data, i am searching for text "home" against every airline among all countries and want to allocate home country to each airline (which is top row). Is there any formula which can do this?
AE - United_arab_emiratesAF - AfganistanAL - AlbaniaAM - ArmeniaAA - AMERICAN AIRLINES AwayAwayAwayAwayAAZ - AMERICAN AIRLINES C/S AA NON OAwayAwayAwayAway AB - AIR BERLIN GMBHAwayAwayAwayAwayABZ - AIR BERLIN PLC C/S NON AIR BERAwayAwayAwayAway
I have two price lists in workbook. One containing "normal" price list, other containing "action" prices.
I want to search for a product name code from column A in "normal" price list in column A of "action" list, and if it is found to show it somehow in any column in "normal" price list sheet.
This way I know that there is action price attached to that product and that I have to search for the price in "action" sheet.
I have two price lists in workbook. One containing "normal" price list, other containing "action" prices.
I want to search for a product name code from column A in "normal" price list in column A of "action" list, and if it is found to show it somehow in any column in "normal" price list sheet.
This way I know that there is action price attached to that product and that I have to search for the price in "action" sheet.
I have a bill of materials with a description column. I want to search that column for various words (ie. wheel, screw, spacer, shelf, etc) and return a value into another new column depending on that value (wheel inputs wheel, screw inputs hardware, spacer inputs hardware, shelf inputs shelf).
How Excel shows you how to search will only return one value because I can't use an else statement:
Want a single count of multiple columns based on the columns selected value. Data is in text format.
Have tried multiple COUNTIF statements and have tried using pivot table (Excel 2010) both only give me total counts for all. I think I need an OR statement somewhere, but not sure where?
In other words, if a single record has an "any" in the any fields or a "yes" in the yes fields, I want to to count that as one record.
I have a workbook with multiple sheets in it. One sheet is an Overview sheet, and the others represent the months (JAN, FEB, MAR, etc). On the Overview sheet, I have a few columns of data set up that I need to retrieve from the various sheets within the workbook. The thing is, this data can occur more than once on any particular sheet and it's very likely to occur multiple times throughout the workbook. For example, compressor #1 was blown down on January 1, January 15, February 5, February 9, February 12, April 22, and so on. Compressors 2-5 were also blown down multiple times throughout the year, as well as various other equipment that I have listed out throughout the numerous monthly tabs. On the Overview sheet, I have the columns Equipment, Date, Temp, Begin PSIG and MCF Loss. I'm needing to get the information from those tabs into these columns.
I first tried a simple VLOOKUP. After all, it's worked for so many situations similar to this, why wouldn't it work here? I quickly found that it won't work here because I need to return multiple results, not just the first one it comes across.
When I copy the formula down it seems to return the same results in every cell, not the next result, and the next, and the next, and so on.
Basically I have a workbook that contains two worksheets, sheet1 contains flight numbers in column A and column E contains the Charge code that belongs to the flight number. The problem I have is that in some cases the flight number can appear more than once in the column and the corresponding charge code is different. Is there a way of using a look up to find the flight number and bring back the corresponding Charge code in sheet2?
Please note I have attached a summorised version of the workbook, the actual Travel# in sheet2 is in column R and the Charge code in sheet2 is in column AD.
I have list of 2043 rows and i have like 29 values i need to look up and return me several values that associated with that particular number but I want it to list them horizontally or even vertically but whenever i get the right code in one cell and i drag that green box it formats it in a weird way that gives me an error.
Here is the sheet I would like an VBA or macro.... my VBA code is already on here but somehow i get an error.
Book3.xlsx
So what i did was the first code followed this example still get errors [URL] .....
So match pcode columnsA to find and return the columnB result somewhere else on the sheet list them out horizontally preferred or vertically.
I'm looking to create a UDF called ListItems which will look through a range and create a one column contiguous list of non-blank values.
e.g. below is Range("A1:C3") --> ignore the Column Headers, they're just included for visual reference.
Column A Column B Column C
John Peter Mary Andy Dave Karen
If the user used =ListItems(A1:C3) in cell D1, the result would be:
Column D
John Peter Mary Andy Dave Karen
Couple of things to consider:
1. The user would most likely have to confirm this function with Ctrl+Shift+Enter --> that is fine
2. A UDF might not be the most efficient/suitable solution to this "problem", but I'm currently learning about UDFs, so am more interesting in how they work, than determining the most elegant/correct way to resolve this issue.
3. The order of the resultant values which I have used above (i.e. reading the range passed to the UDF from left to right one row at a time), is not critical, if it is easier to read top to bottom one column at a time, that would be absolutely fine too.
I have an array 20 Rows x 42 Columns, which contains a competition draw.
I need to search this array for a unique value and return whatever the time is in the first column on the same row as the value appears, and enter it into column C in the Womens Times sheet.
I also want to return into column D the court number from row 3.
The reason i want this automated is as teams enter / withdraw we may need to drag the games from court to court to fill gaps, so i want the Womens Times sheet to update accordingly.
I have been messing around with index and match, but cannot quite get it to return what i need.
I have attached an example ... on the sheet "Womens Times" in column A there is a list of game numbers ( #1W etc etc) indicating womens game #1 and so on. The main sheet i am using also has a seperate tab for the mens games, hence the designator of W or M on the end of the game number.
I have a list of countries in column A, there are duplicates in this list. In column B are numbers corresponding to the country.
A B IRELAND 1221 GERMANY 2341 USA 3443 IRELAND 2231
Ireland could be in the list and have up to 20 different values beside it in column B
On a separate sheet i have a drop down list of all the countries. I am looking for a vlookup or something similar to return a list of all the numbers beside IRELAND when Ireland is selected in the drop down list.
I had been trying a vlookup but this looks at the list and stops at the first "IRELAND" it finds and just returns the value beside the first "IRELAND"
I am trying to write a formula that is based on a vlookup that will return multiple values. Attached is a spreadsheet that is an example for what I am trying to do. On the download tab it is an example of a general ledger. On the total tab I want to enter a GL# and have it list all the entries for that GL number. I know I can try an array, but I have not been able to figure that out.
I am remodeling my home and have put together a budget template. I have uploaded the file to make things easy to understand. Basically I want to return the values from column F and G on sheet "ledger" to the corresponding account name to column H in sheet "budget". For example, rows 14, 21, and 22 in the "budget" sheet currently correspond to the "fuel" account. I would like those values in column G (or F) to be added and calculated in cell H58 in the "budget" sheet.
I managed to found an array formula that return multiple values with a condition. Below the formula:
[Code]....
I am attaching a simple file. In cell O4 I paste the formula, and I drag it as much as I need. But my issue comes when the formula begins the calculations. I need to calculate 50 columns with over 9,000 rows each column (over 45,000 arrays). Imagine the processing load to my computer and the consuming of time.
Is there a way to use a lookup formula to return all values of a given text, not just the value of the first listing of the text. In an attempt to clarify my already confusing request, if A5 on "worksheet1" is "Land" I want E5 on "worksheet1" to return the summation of values assigned to "Land" "worksheet2" "column B" any time "Land" appears on "Worksheet2" "Column A".
I have a workbook with 2 sheets. The sheet called 'data' contains all purchasers names (which do get repeated), the item they bought and 3 item costs, all in range A1:E15. The 'lookup' sheet allows the user to enter a purchasers name in cell B1, then I want all the items that purchaser bought listed with the corresponding item costs. I've attached a sample spreadsheet.
