I have a column of week numbers but some gaps in the list (e.g. the job is complete and so I do not want to reference it) I am trying to create a list of jobs by week number. I need to sequentially number jobs to then use Vlookup e.g. job1-week1 job2-week1 etc to display be week.
I can work out to number the list ignoring the blanks but then resetting to 1 with each new week?
Workbook1.xlsx
I have a set up of investments. Say A1 through to G1. Then on the next row the investments range from A1 to G1 again. This continues down many rows. There are spaces such as D1 and D2 through E2.
A
B
C
D
E
F
G
1
ausbil........................
Starting in column H, I would like to list the investments but ignore investments that have already appeared on the row (ie list only the first occurence) and ignore the blanks as well. For example, D1, E1, F1 are to be ignored because of the blank, and the fact that ausbil and amp already appear.
In row 2, the blanks in D2 and E2 are ignored as well as the repeated 'amp' in F2. So the table should look like below from column H:
H
I
J
K
1
ausbil
amp
vanguard
bt
2
amp
becton
fidelity
bt
Is there a simple formula for this? It has to be in rows as I have at least 60 columns of data before these columns begin. There are at least 300 rows.
I have this array formula
=IF(ROWS(A$11:A13)1,IF(MSB!$A13"",SMALL(ROW(Table1[APP DATE])-ROW(MSB!$A$11)+1,ROWS(A$11:A13)),""))))
the problem is that when both if statements are true i want it to get the row number, this is because table1 has blank rows (can not be avoided as the data is linked from a closed workbook)
i can see this works up until it meets the first blank row and i get #value error.
on table1 data exists on row 1,2,5 and 8 so my problem is getting the small function k to report these numbers?
I have a column of numbers that are derived with a formula. I need to Average only the ones that either have a Positive or Negative number, ignoring blanks or zero.
I have tried Search but couldn't find anything that address both blank and zero.
Sheet2
K610.00%624.76%632.53%6418.75%65666.38%6768-4.00%6970-5.84%710.86%
Excel tables to the web : [URL] .......
With this small sample, the answer should be 3.35% according to Excel when I choose just those neg and pos cells.
I am using a simple SUM formula like this, =SUM(D10:E10). I would like for the SUM cell to be left blank until a value is entered into the cells D10. Currently it calculates the SUM as 0.
View 13 Replies View RelatedI want to count back the last 6 cells in column S which has data (ignoring any blank cells) and add them up.
My data is ranging betwen S62:S143 (with S144 being the cell that I want to show the total for the last 6 cells with data). I have column headings and various other bits of data from S1:S61 that I want to exclude completely but need to keep for historical purposes.
Here is an example of some of the data in column S:
56
BLANK CELL
BLANK CELL
BLANK CELL
45
34
BLANK CELL
BLANK CELL
37
BLANK CELL
BLANK CELL
42
BLANK CELL
46
49
BLANK CELL
BLANK CELL
36
35
This is my formula so far:
=SUM(OFFSET(S62,0,0,COUNTA(S62:S143)))
I have tried adding a negative and positive number on the end of the COUNTA formula but all it does is count all the cells within that given heigh range, not a specific number of cells with data.
I don't specifically need to use SUM. As long as I get a total.
Trying to determine the best way to do this. I understand that the standard AVERAGE function will ignore blanks if given a range; the function I'm using does a search for a particular value to determine if a value is to be included in the averaging: ...
View 9 Replies View RelatedI used each of the following codes for dynamic name ranges.
View 4 Replies View Related=IF(Z2="","",Z2&","&IF(AA2="","",AA2&",")&IF(AB2="","",AB2&",")&IF(AC2="","",AC2&","))
I'm using the above formula to join text from columns Z through AC, separated by a comma. I now want to remove the comma at the end of the new string. Also, I would prefer it if the four cells were separated by a slash rather than a comma, but when I simply replace the commas in the formula with slashes I get an error.
Example.xlsx
I am wanting to have a sheet that I use for reporting based on another sheet in the workbook but I want to exclude items that are closed.
The first sheet will contain all rows of data with a status column of either Open, On Hold or Closed
In the second sheet I only want to have listed the items which are Open or On Hold, I want to ignore the closed items.
In the example enclosed, I only want rows 2,4,6 and 7, but NOT 3 and 5 due to status.
I have a spreadsheet with a bunch of formulas in Cells B4:E100 in Sheet1. Many of the formulas will show up blank information in the event I do not have any data in Column A. I then have formulas which perform a similar function in Cells G4:Q100, S4:AC100, AE4:AO100 & AQ4:BA100.
What I need to do is to take the data from the last 4 sets of cells (G4:Q100, S4:AC100, AE4:AO100 & AQ4:BA100) and stack then one on top of the other into Sheet2 starting in Cell A2. I need the code however to be smart enough to realize that if there are blanks which exist in any of those sets of cells to skip over to the next set of data I which to copy and start pasting that set of information without leaving any blanks.
So far example, if I only have data between Cells A4:A25, I want the VBA code to go only through cells G4:Q25 and then start pasting without any spaces S4:AC25 and so on.
I am trying to create a weighted average which will skip any row when Column B say's "yes" and then if Column N contains, a 0, I would like that to be skipped as well. The below works for skipping any row with the word "Yes", but it still includes 0 in the weighted average. Also, let's say the Column N contains a word and 0's, how can I skip that?
=SUMPRODUCT(($B$13:$B$15="Yes")*($I$13:$I$15)*(N13:N15))/SUMIF($B$13:$B$15,"Yes",$I$13:$I$15)
I'd like to do is to use loops to copy each value in "Sheet1" into every other row in column B of "Sheet2" starting on row 16. I realize that this is a simple task, but I'd like to know how to do this for the purposes of learning the correct VBA coding.
View 7 Replies View RelatedI am trying to work out how I can assign a sequential value to sets of duplicate data within a list. i.e. where I have duplicates in a list I want each of the duplicates (that are in the same set) to have a sequential number assigned and for that to start from 1 each time it discovers a new set of duplicates.
RED
GREEN
YELLOW
PURPLE
RED
ORANGE
BLACK
GREEN
RED
Where a colour is duplicated in the list I want it to have a sequential number assigned. When a new set of duplicates is found the numbering starts from 1 and sequentially increases. So the above data list would resulting in the following output
RED 1
GREEN 1
YELLOW
PURPLE
RED 2
ORANGE
BLACK
GREEN 2
RED 3
Have tried variations of the RANK function, but not sure how to get the sequential nu,bering to restart with each unique set of duplicates. I would also like to do this without VBA if possible using the built in functions.
I need to lookup data based on a cells content. The answer has to output to a table as a sequential list. I have attached a sample workbook (there will be 100+ sheets in the final version!). The Register sheet lists competencies against page# and title. For each Evidence Sheet I need to populate the Competency grid (C4:D10) in a sequential manner i.e. no blank lines,and the Title (B13) based on the Page # (D2). I have looked at a great number of postings, but cannot see a solution that fits.
View 7 Replies View Relatedhow many accounts, and list the total on the bottom.
So, I have something like this:
10003722
10003722
10003722
10003722
10008981
10008981
10008981
10008981
10008981
10028163
10034527
I need the column next to it to show #1 next to 10003722 than jump to the next one and have 2 next to 10008981 and so on...
Is it possible to autonumber records created with data entered with a user form?
If it is possible - I would also like to know how I could display the current record number on the data entry form....
I have a dynamic drop down validation List, which comes from another
spreadsheet list and have a few random blank cells in it. I need to
drop these blank values from the List without making any change in the
parent list. I may also have some duplicate values in the List and need
to drop them too.
I would like to exclude blanks from the source of a validation list, I have found a really neat solution for vertical list in this topic, I have modified it, but I have missed something, the formula is not working.
View 2 Replies View RelatedI have a list of names and an associating number with those names. I'd like to create a dynamic top 10 list, but my problem is that I have duplicate values, here's a sample:
[Code].....
I tried to use LARGE and INDEX to create a top ten list like this:
=LARGE(Names,ROW(1:10))
=INDEX(Names,MATCH(T2,Count,0),1)
But, when the list comes out, it shows only the first entry with a duplicate result:
[Code] ....
How I can get it to grab the second "Name" with the same value as the first?
I would like to reorder a list whilst ignoring any 0 values. I've attached (I hope correctly!) a spreadsheet of what I mean. It's pretty simple, but I've been banging my head over it for a while now!
Sample.xlsx
I don't know if this is best in a formula or macro but what I am trying to do is create a list of data with no empty cells from a list of data with empty cells.
What I want to say is if A1 is blank move to A2, if it's not return the value in A1 to B1. If A1 and A2 are blank then return the value in A3 to B1 and so on. Generally it's a sort function but I want it to happen automatcially and lkeep all my values in the original order
Original Version:
Date 1/4 Value 1/4
23/02/199913.506
5/05/199911.901
14/05/199912.152
24/05/199911.607
7/06/199911.187
29/07/199911.828
2/09/199910.473
6/09/199910.429
Desired outcome:
Date 1/4 Value 1/4
23/02/199913.506
5/05/199911.901
14/05/199912.152
24/05/199911.607
7/06/199911.187
29/07/199911.828
2/09/199910.473
6/09/199910.429
I have a sheet with a data in range A1:J9. I have a drop down list in each cell from A2:A100.
I'm trying to store all values in A1:J9 in an array using the property CurrentRegion.value like below
Code:
Arr=Range("A1").CurrentRegion.Value
The issue is that is taking as current region the range A1:J100, even if I don't have selected any value in the drop down list from A10:A100.
Is there a way to force CurrentRegion to select only values from A1:J9 ignoring the blanks dropdown list values or a similar way to load an array with values in a contiguous range?
i have a list which is populated with data from another sheet.. if there is no data in the corresponding cell on the other sheet then the cell is blank.. i've tried using:
=OFFSET($Z$2,0,0,MATCH(REPT("z",255),$Z:$Z))
but it doesn't see the blank cells as blank i.e. no data in them as they contain formula's.. (even although there are no values populated) - can anyone offer any help in relation to this???
I have a spread sheet where there are multiple rows of price quotes from different suppliers.
Some suppliers have quoted, some however have not and there is a "#N/A" or "no bid".
SupplierA
SupplierB
SupplierC
SupplierD
1.3
No Bid
2.3
No Bid
How can I get a conditional format to check each row for the lowest numeric bid, highlight it but ignore the text?? What I have tried so far doesn't work.
=G31=MAX($G$31:$J$31)
This does not seem to work, I don't know how to exclude the text values from the MAX function.
Data is in a horizantal list, say row 2:
A2: SAM
B2:
C2: MARY
D2: JOE
E2:
F2: DAViD
i.e. like this:
SAM-blank-MARY-JOE-blank-DAVID
I am looking for formulas (not VBA) to compact this list by removing the blanks so the new will be placed in K2, L2, M2 etc. as follows:
SAM-MARY-JOE-DAVID
This is a good article but only works on vertical lists:
[url]
I think this must be fairly easy to do. I've found various posts here and tips elsewhere that indicate ways to do this, but I've not found one I could get to work.
I have a column with numbers in it. These numbers are formatted as TEXT (because they are 001, 002, etc). This list is generated by pulling the unique values from another column where these numbers are repeated.
In the following example, B has the original data. H has the same data with just the unique values. That is how far I have got. What I now want is what you see in Column I (the unique values with NO BLANKS in it).
B | H | I |
-------------------------------------------
001 001 001
001 002
001 003
002 002
002
002
003 003
003
003
I am aware that I can use an Advanced Filter (not that I manage to get that to work without it generate an erro). But this is not what I want, as I'd like the data in I to be dynamically generated such that if more unique values are added to B then H will reflect this (it does that at present) and then I will also reflect this.
I need to generate a list of items from a column of data which frequently changes. In the attached example, I need to list all entries other than those that are blank (NB - the data generates the numbers as text and the blanks as "").
View 8 Replies View RelatedI have a master list from which certain items are chosen and displayed in several other lists based on always changing conditions. The way i have these other lists set up displays the items in the same row as the master list using IF Formula nested with Exact functions to qualify it. If the item isn't qualified to be in a list then I get a blank cell. Is there any way to consolidate these lists so they don't have lots of blank cells?? I would like it to happen automatically as my lists are always changing.
View 3 Replies View RelatedHow to fill or copy contents of
View 4 Replies View Related