I have a form that will open a workbook and then run a function to replace items in a column based on the column name. The problem is that it only replaces some of the items.
Here is my click event that calls the function
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
I need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
I am trying to find a way to automatically find and replace 2 letter symbols in a specific column into the words they represent. There are many symbols and rather than do a find and replace for each symbol, I would like to write a macro that would do them all at once.
In the attached file, details sheet contains multiple instances of project with associated costs for each of 2006, 2007 and 2008. What I need is a formula (preferably) or a VBA that select distinct project names and populate column B of summary sheet so I can do a sum if. The problem is the project names changes dynamically every week and they are practically in hundreds.
View 9 Replies View RelatedI have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!
For example from this
invoice #1 / reconciled (DATE)
invoice #2 / reconciled (DATE)
To this:
invoice #1 / paid (DATE) check # (xxxxx)
invoice #2 / paid (DATE) check # (xxxxx)
I have a table in the format below. Pin_11 in this table is CLK and Pin_12 is STRB. I want to replace Pin_11 with PIN_CLK. Basically I want to look for CLK in Column3 and record what ever is in Column1 and Column2 on the same row (base row). For every repetion of that value in column1 and column2, in the rows above and below the base row, I want to replace column 2 with PIN_<column3 value>.
See below for initial table in column 1,2,3 and final result in column 6,7,8. I have a huge file this format of data that I need to end and was wondering if there is a script or formula to do it.
CARD NO
PIN NO
TYPE
CARD NO
PIN NO
TYPE
CARD_2
PIN_11
[Code] ....
I have a list of items in a column. I would like to have a userform pop up with a list box (?) of all the items and I want to be able to select multiple items to perform an action on the row corresponding to the selected item.
I am thinking of other examples I have seen where there are two list boxes (?) and then arrow buttons between them. When you click the arrow the item moves from one list box to the other to see which have been selected.
I have never used list boxes before so if you have a link to some sample code that would be useful.
1. I need to count how many different items within one column, these are formatted as Text. cells G63:G1600
Sample text:
F41D6
F44DD
FABHD
FAXHD
FDTGD
FFDGD
FFTED
FGDFD...................
2. I need to count how many dates fall during the week using the ending dates. Weeks ending 1/06/07, 01/13/07,etc L2:L53, the dates to count are L63:1600.
Sample Text:
09/05/07
09/21/07
09/07/07
09/17/07
09/21/07
09/05/07
10/03/07
09/13/07
09/12/07
09/18/07
09/18/07.............
Table 1 below was extracted from my monthly report of remaining items in our inventory as of month end. This was automatically generated by the system and I am exporting to Excel for editing. We have around 20,000 items.
My final report should look like Table 2. I have been trying to create a formula to expedite the process but I was not successful. I was concentrating using the "IF" function.
TABLE 1
PART NO/
SRV NO DATE
123 4
SRV-1 1-Jan-12
[Code]....
47.00% Defense
Defense
83.00% Defense
answer here
54.00% Attack
26.00% Attack
19.00% Defense
I am looking for a formula that will calculate all the Defense in column A if Defense is in B1 and calculates all the attack in column A if Attack is in B1
I need a quick way to count the number of items in a column
For example
COLUMN A COLUMN B
apples 3
oranges
pears
bicycles 4
cars
trains
buses
There are over over 17.000 rows
I want to find an instance of a word in one column......and replace whatever is in the adjacent column with a word.
i am looking for formula in my macbook pro excel where i have purchase apple 50kg for $50 and grapes 20 kg for $30 and orange 10kg for $15 and am doing this daily and in end of month i want to see separate kg's and amount for whole month and later year of apple, grapes and orange without sorting it
View 1 Replies View RelatedI would like to map each item in the row with the column header and the price for that item for the customer, similar to the below one, go through both the work sheets, i want the output in the rowitem view.
SKuIDItemNameCustomerRate
11682Item 1Cust 1312.56
11682Item 1Cust 2411.27
11682Item 1Cust 3411.27
[code]....
As per attached file, there are altogether 4 sub-files and 1 master file.
I'll need the code to automatically pick up the top 9 revenue(highlighted in Green) and top 9 EBIT(highlighted in Yellow) by values in Column N from 4 sub-files and paste to the master file.
Attached File : Example file1.xlsx
I have two columns with aproximately 80,000 rows each lets say column A and D.
Column A has numbers and it is the most recent data extracted from a program. Column D has also numeric records most of which are also in column A.
I need a macro that will take each number in column D and compare it to column A when the macro finds that the number in D exists in column A it shoudl move the number on the same row on column B (right next to it)
If the number does not exist on on columna a It should start a list on column C.
Looking for a formula that will give me a count of unique entries (i.e. duplicate entries to not be counted.)
basically a formula that says look in D4:D4001 and count only one of each entry....do not count the duplicate entries.
I have made a report in pivot table. My Headings
Sales A Sales B Sales C
Region Name
Location Code
Location Code
Category
Location
Cluster Head
After made the pivot i started the grouping but is ther any limitation for excel for this? It is showing the below message. "Microsoft Excel cannot make this change because there are too many row or column items. Drag at least one row or column field off the PivotTable, or to page position. Alternatively, right click a field, and then click Hide or Hide Levels on the shortcut. How to over come this and do the grouping. Is this limitation of Excel. I am using Office 2000
There is a list of interconnections of panels.
For example:
From To
..................
P1.........P4
P2.........P4
P1.........P5
P1.........P4
P3.........P5
P2.........P4
P2.........P4
P2.........P3
There are hundreds of sheets like this with more complex panel numbers (there are 70 to 80 panels). I want to count the number of times each panel is linked with another panel i.e the number of times its name is mentioned in front of it.
This may look like as follows.
............P3.........P4.........P5
P1..........0..........2...........1
P2.........1...........3...........0
P3..........0...........0...........1
One of the columns on my worksheet has a list of employees. I need to replace all the names with a blank (or delete) except for those employees on my team (7 employees). VBA code to Replace/Delete all EXCEPT (a,b,c,d,e,f).......?
View 2 Replies View RelatedI have a column with both positive and negative numbers, is there a macro or a bit of code that will convert all negative numbers in a selected column to zeroes?
View 3 Replies View RelatedBefore
Company segment
AAA A1
BBB A2
CCC A3
AAA A1
After
Company segment
AAA A9
BBB A2
CCC A3
AAA A9
how to use Excel macro to change segment Colume with company name " AAA " , segment code from " A1 " to " A9 " ?
I am trying to find a way to code to replace any cell that is not blank with the text "LOA", then after I replace all blanks with "Active"
The Replace blanks is easy:
Columns("K").Replace What:="", Replacement:="Active", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
but before this I dont really know how to quickly replace all non blank rows. Only way I know is to loop thru all cells in the column checking for not equal to "" then replacing, but the code above for replacing blanks is elegant, and only replaces in the used data section.
In Column A I have a list of Products and in Column B is the list of corresponding prices. I'd like to create a drop down list with the items in Column A that would automatically fill-in the pricing from Column B next to it. I've tried using an IF statement, but my product list is too long.
View 5 Replies View RelatedHow do you make a column automatically place items in alphabetical order?
Also, how do you make a column automatically put dates in order?
I am trying to count the number of unique items in a single column (~5,000 rows of data). For example, I may have the following data
a1 = apples
a2 = pears
a3 = oranges
a4 = apples
a5 = apples
a6 = apples
a7 = pears
in this case number of uniques items is 3
Right now I am using a Pivot Table to figure out the number of unique items but I am sure there is an easier way to do this.
I need to be able to pull the information into another section of the workbook and place it into order (ie Black 1,2,3,4) and then drop down to the next row and place Blue 1,2,3,4 etc. I am using a drop down menu to select the Course, and want the times to then populate to the right, each in its own cell.
Have tried the loop code available on a few different topics here however has not quite worked.
I need to be able to count items if they match a condition in another column, as below
A B
1 Apple Red
2 Apple Green
3 Apple Red
4 Pear Green
5 Apple Red
6 Pear Green
7 Pear Red
8 Apple Red
How can I count how many Red Apples there are. The answer should be 4 but when I use countifs I can either count the no of apples in Col. A or the no of colour Red in Col. B.
I have tried every combination of if, vlookup, and count that I can but keep getting errors or single col. counting.
How do I add the column heads (e.g. A1, B1, C1....) as items to a combobox or listbox if they are not empty?
View 9 Replies View Related