Blank Counting Amongst Two Data Columns
Oct 17, 2013
So, in general I do have two columns F and G taken from the other xls.
Age is obviously difference between today and open date
Open date is open date.
I made a table like:
Age and due date.png
In this case, I have 3 rows where there is no open date extracted, therefore is no age. The counter stops on them and shows 529 in total instead of 532 or shows the age as far more than 365 days. How can I count the blank cells, but only in the range of the list I do have, not the all blanks I have from the beginning till the end of the column, so I could (for similar in this case) have 3 blanks cells counted?
Sometimes is also stuck in the middle of counting (when blanks are inside there) and the total number is even smaller. What function can I use to count these 3 (or less, more inn the future) as BLANK to have the total numbers realistic?
View 6 Replies
ADVERTISEMENT
Mar 17, 2009
In my table I have a column called Series and Column called Games.
Now when a user makes an entry the do the following
Enter the Date of a game
then a Series # (I wish I could make this auto increment because a series number can never be used more than once.)
then a game number starting with 1 and never going past 3.
Then Bet Team, Home or Away and Vs Team.
Now let it be know if the column called w/L = Win then that Series is over for ever and there will be no more games played.
If there W/L = Loss then I must create a new row for that SERIES with the next game number.
What I was hoping to do is
If I write in the following going in order
Date ---4-11-09
Series # ---- 6
It should auto populate
Game to the next game #
Bet Team
H/A
Vs Team
I also want it to update my record
C3 is my series Wins
D3 is my series Losses
I do not know how to populate that.
A Series Win = during a series game 1-3 where W/L = Win
A Series Loss = a series 1-3 where there are no wins. A series does not count as a loss until you loose game #3.
View 10 Replies
View Related
May 23, 2014
I need a formula that will look into a range of data and tell me whan the last time a value exceeded 0 (working backwards).
So below the first row would return a value of 6, the next 5, the next 0, the next 1 and so on....
I can do it with an if formula but the amount of days it will be looking at will be too many, plus the range will keep growing as time passes.
FriSatSunMonTueWedThuFriSat
222000000
111100000
111100011
110111110
000111111
000000011
111111111
111111111
5117400000
564000000
8110660000
0000018171318
View 3 Replies
View Related
Dec 19, 2012
What I am trying to achieve using the example below:
ID Subject 1 Subject 2 Subject 3
1 Italian French German
2 Italian Art Physics
3 German French Italian
4 French Italian German
the result:
Italian French German 3
Italian Art Physics 1
As in the example, the combinations of Italian, French and German where counted, irrelevant of whether the subjects are in 2nd, 3rd or 4th column.
I tried to do this task by creating a pivot table but there are so many permutations and subjects that it would take me a long time to add the combinations.
View 6 Replies
View Related
Nov 7, 2008
I need to select all data in columns A:F but the number of row containing data is variable. I need to select only the rows with data.
View 7 Replies
View Related
Apr 13, 2012
I have a large table about 2000 rows x 2000 columns (small extract below) and i need to get all the non-blank data out of it into just 3 columns ignoring any blank cells. Column A is the customer account number and the other columns contain the prices for the part numbers as described in row 1.
a/c - part no
002000
S01000
S01020
S01040
S01060
S01080
[Code] .......
What I need to end up with is list of Account/Part No./Price like this...
A/C
Part No.
Price
00017391
S01020
7.2100017391
S01060
10.4500017391
[Code] .........
There will be about 70,000 lines from the table I have.
View 5 Replies
View Related
Jul 10, 2014
I have some data from many csv files.
I would like to record/create a macros to open the file and paste 4 columns worth of data into another spreadsheet. Making sure the data is pasted in the correct column and doesn't overight data already in a tracker. Ie. paste it into the next available space.
Output.csv
View 9 Replies
View Related
Feb 10, 2014
We utilize large data sheets that can be as large as 300K in rows and 10 to 15 columns wide. Because of how we receive the data, we are forced to manipulate things so that all matching data for a record ends up on a single row. (e.g. Record#, Document Type, Husband Name, Wife Name, Wife Maiden Name, Etc.)
Right now here's how the data is received:
a
a
a
b
b
c
c
c
c
d
d
d
e
e
e
e
Using two vba scripts, we first separate the data with row spaces between the unique data as follows:
a
a
a
b
b
c
c
c
c
Then with another script, we transpose the data as follows:
a a a
b b
c c c c
d d d
e e e e
When we transpose the data, the end result starts at the top of the page and go down eliminating the original blank rows. Not a huge issue but I would like to be able to maintain the original data format of the rows so that the data matches the original sheet line for line. The end result would give me the data as follows...
a a a
b b
c c c c
d d d
e e e e e
where the vertical gaps between the letters matches the original rows. Like I said, not a huge issue since we can rejoin the transposed data to the original data fairly easily. But it would be nice if we could end up with the above format for speed sake.
The two scripts we use, one-to insert the rows and two-to transpose, take a very long time to run with the transpose script taking the longest by far. On a 30K row sheet, it will take on our systems around 30 minutes to transpose and about 15 minutes to insert rows. Because we have several columns that need to be transposed, a 30K row sheet will take at least 2 hours to complete. A 300K row sheet, that will take 10 to 15 hours to complete.
Is there any way to speed up the scripts either by upgrading to a faster CPU and or writing the scripts to preform faster?
My preferred solution would be to write (have) a formula to preform the transposition that gives me the results as noted above since formula's run so much faster than vba. Is this possible? I have tried all kinds of formulas and can not come close and of course the straight transpose function does not give me the solution I need as noted above.
I have enclosed an excel 2010 spreadsheet with 10K rows of data in rows along with the scripts I use (nothing sensitive here). The tabs at the bottom shows you the data before I transpose, then the data after it has been transposed . To speed up the scripts, I have stripped away the all the rest of the data from the original sheet except just what I need to transpose at one time. Once that is completed, we then re-join the transposed data with the original sheet. The six digit number you see to the far left of the data is the record ID number from the original data. We use this to rejoin the transposed data with the original data so that we know everything is back where it should be. (Note: The insert rows script is run on the original data and not the data you see on the enclosed spreadsheet. That is the only way we can generate unique rows with matching ID numbers. We arrive at this by taking the original data, concatenate the record ID with the column we want to transpose and add a # between the two so that we can break things back apart after the transposition using the text to column function using the # as the separator.)
The sheet I have attached is in the 2010 Macro Enabled format...(xlsm format). We use the xlsb (binary) format for the data to reduce the file size as our normal procedure and run the macros from inside that format. Changing from the xlsx to xlsb format did seem to speed up the scripts a bit and greatly improved the file performance as a whole e.g. saving and loading.
One thing I have done to speed up the scripts is to strip all the data away that is not needed for the transposition. That did work but only a marginal amount.
We are using windows 8.0 with 4G memory and your basic processor speed...e.g. nothing fancy.....just your basic stock computer. Nothing else unusual is installed or running on the computer or at the time the scripts are running.
For those of you that process large sheets, how much of a performance upgrade will we see in processing our scripts by either upgrading memory to 8G (or more or much more) and or getting a faster processor? Or have we reached the maximum script speed already? Or is this a limit to Excel.
One other issue to note: As I stated above, on the 30K row sheets, not a super problem with about 2 hours needed to run the scripts on all the data on the sheet. But on the 300K row sheets, it can take 12 or more hours to run and there are times when things 'lock up' running the scripts on sheets this size.
View 5 Replies
View Related
Oct 13, 2008
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.
View 4 Replies
View Related
Dec 5, 2008
I am trying to count the # of rows in a spreadsheet in which there is non-blank text data in 2 separate columns. For instance, if the spreadsheet looked like the one below (dashes just for formatting purposes):
NAMES-------THIS-------------THAT
Mary-------some text--------some more text
John-------<blanks>---------just text here
Sue--------just some here-----<blanks>
Dave-------something--------something else
Adam------<blanks>-----------<blanks>
The total # of rows with something in both the "THIS" and "THAT" columns above would therefore be 2.
View 4 Replies
View Related
Feb 4, 2010
I have attached a spreadsheet with some tele numbers. What i need to do is to find out which column only has 1 telephone number, eg. only a cellphone number, etc. I have aboout 50 000 records that i need to check if only 1 telephone number is available. Can i use the countblank function?
View 2 Replies
View Related
Nov 25, 2006
I am trying to write a formula for the following can anyone help please?
I have column D with blank cells and some with purchase order numbers in them. I want to count the blank cells in column D but only if the blank is 3days older than the date recorded in column A. Once I have done this I want to count ther same on every work sheet and I have 24 off them and get one answer.
This will tell us all the outstanding purchase order older than 3days
View 9 Replies
View Related
Apr 13, 2013
I have a Colom with dates, some cell are coloured and others not, some of the non-coloured cells have dates and some not. I would like to count ONLY the non-coloured cells without dates in them.
View 6 Replies
View Related
Sep 1, 2009
I am trying to put together a table from which I'll attach a chart showing-at a glance-where open complaints are in our process:
are they open, closed, waiting for samples, waiting for QC, QC'd pending admin approval, etc.
my log consists of a lot of columns which track various pertinant info about each complaint. Such as A-complaint ID, B-date opened, C-date closed, K-initial QC investigation, L-final QC, M-admin status (pending/approved/denighed), N-notes (boiler plate notes like "pending ra samples" or "closed").
I used this formula alot in my various analyses =SUMPRODUCT(--(YEAR(Log!open)=$O74),--('ComplaintLog (101408).xls'!iqc="pending")) without issue.
Until now. I want to search multiple criteria starting with the year (2009, in this case) and whether or not the issue is closed (date in column C). Only need a count.
How do I append the sumproduct formula? I've looked at isblank but get crazy results from #value to #name to 340 (I only have 292).
View 5 Replies
View Related
Nov 9, 2009
The code down below counts all cells from column A2 to I11. The problem; the code should stop counting if there is a blanc cell in a column and go to the next column to countinue. My query; how should the code be modified so it will stop counting if there is a blanc cell.
View 4 Replies
View Related
Nov 29, 2011
PeriodWeek #DayDateNotes11Thursday29-DecBB vaca11Friday30-DecBB vaca
Tab1
PeriodWeek #DayDateNotes11Thursday29-DecBB vaca11Friday30-DecBB vaca
Tab 2
I have the column E in excel labeled 'Notes' in tab 1 adding at the bottom of the column using(=COUNTA(E2:E29), so if the cell is empty it doesnt add it up in the totals. In tab two i have that same column linked via formula. In tab 2 its counting the empty cells because even though they appear blank they have a formulas in them how do i get it to not count those cells. here is the formula im using in column E tab 2.
=IF('tab1'!E2="","",'tab2'!E2)
View 3 Replies
View Related
Jan 18, 2013
I have a spreadsheet that is used to capture information about Service Requests that my team handles in a 6 month period. At the end of January, the metrics for January will be added and the metrics for July will "fall off".
I need to be able to calculate the number of requests that were still active for each month. The formula I have created for this is below:
=COUNTIFS(Table1[Date Submitted],"Month Beginning of the First Month Being Reported", Table1[Date Closed], ">A Day Before Current Month Closing")
So, if my reporting period was July 2012 to December 2012, my formula for December would look like:
=COUNTIFS(Table1[Date Submitted],"7/1/2012", Table1[Date Closed], ">1/6/2013")
This accounts for anything that was opened in the months that was not closed before the month closing of the current month being reported.
I have two issues that I'm having with this formula:
1. The formula does not take into account the Service Requests that do not have a date in the "Date Closed" columns. Is there a way I can also count the items that have a blank "Date Closed"?
2. This formula will have to be changed every time a month "falls off". For example, when the reporting period changes from July to December to August to January, the formula for December will change to:
=COUNTIFS(Table1[Date Submitted],"8/5/2012", Table1[Date Closed], ">1/6/2013")
Is there a way I can have it change automatically without me having to do it manually?
View 1 Replies
View Related
Feb 24, 2009
I'm trying to to create a formula where if two different cells have no value (K10 & O10) a third cell will also be blank (N10)
I have the following formula which follows these steps when i evaluate it.
=VLOOKUP(K10&O10,$J$77:$K$98,2,0)
=VLOOKUP(" "&O10,$J$77:$K$98,2,0)
=VLOOKUP(" "&" ",$J$77:$K$98,2,0)
=VLOOKUP(" "$J$77:$K$98,2,0)
#N/A
How can i deliver a blank cell as the answer?
View 9 Replies
View Related
Aug 27, 2009
see attached doc. The graph is showing the overall % from the table above. However months april-december are blank although they have a formula in them. This is causing the graph to show thes months as 0%.
View 2 Replies
View Related
Dec 9, 2009
I have a simple date formula that I want to present "yes" in a cell when another cells date is less then or equal to "Today". I just can't figure out how to make it show my result cell as blank if my date cell is blank?
here is my formula....
=IF(G8 <= TODAY(), "YES", "")
View 2 Replies
View Related
Sep 5, 2009
Using formula rather than VB, I would like to calculate the number of blank cells that appear in a column BEFORE the first active cell but exclude any blank cells that appear after the first active cell. To elaborate, I have sheets that contain the days of each month and I need to exclude for other calculation purposes, the number of days (cells) where no entry of data has been input at the beginning of a month, NOT after the first data entry. ie. September has 30 days.
The first data entry is the 10th and there are no entries (thus far) after the 10th, the result that I seek, will be 20 (although only one cell has data), being the days left in the month AFTER the first entry. I have looked at COUNT functions but cannot find an solution. Perhaps it's not possible using basic formula?
View 4 Replies
View Related
Jan 19, 2012
I have a formula counting non blank cells in another sheet 'Mem' I know there are 21 entries in Column M, which it used to show as the result. Today it shows 0. The 21 entries are still there.
Code:
=COUNTA(Mem!$M:M)
The cell containing the formula is B6. The bottom bar of Excel has: Ready Circular:B6
View 2 Replies
View Related
Mar 21, 2012
I want to count the number of cells in column A that are not blank MINUS or EXCLUDING the cells in Column AD whose values are "Closed".
View 9 Replies
View Related
Apr 2, 2008
why it is counting every blank cell as 1. I have tried to copy my data as paste as special value and run pivot again but I get the same result. For you visual consideration, I've attached a sample spreadsheet.
View 6 Replies
View Related
Jul 7, 2014
find the attached excel 2010 file <Count LBP or UBP.xlsx>.
My requirement is if `LBO' is available (in Column A) then the formula should count the number of `LBP' available above the `LBO' upto the blank cell. If there is no `LBP' above the `LBO' then it should plot zero in Correponding Column B Cell. If 2 `LBP's available above the `LBO' upto balnk space it should plot `2' in Correponding Column B Cell.
As like `LBO' and `LBP' the formula should work for `UBO' & `UBP'. refer the attached excel file.
View 6 Replies
View Related
May 29, 2013
I have the following table
Team>
Team A
Team A
[Code].....
I need to fill the following table in another sheet counting the amount of Blank cell there are according to Month, Team and if the name row is filled. I have tried Sumifs, sumproduct,countblank typing them in as arrays but don't seem to be getting anywhere
April
May
Team A
6
1
Team B
11
1
View 2 Replies
View Related
Jul 22, 2014
I have the following formula which counts number of days between dates in two adjacent cells excluding holidays
=NETWORKDAYS(U311,V311,$AA$4:$AA$14)-1
The $AA$4:$AA$14 refers tote range where the excluded holiday dates are stored.
I have included the -1 at the end as it over counts by one day every time. i.e. if the same date is in both cells it counts 1
However if no date is in both fields it count -1 (minus one)
If a date is just in one field it counts a very high number, example below.
-1
16/07/2014
-29879
20/06/2014
08/07/2014
12
[code].....
is there a better formula to use for this? How do I get the result to show ZERO when both cells are empty? can I get the result to show number of days to present date where there is only a date in first column?
View 3 Replies
View Related
Apr 29, 2014
I have a spreadsheet with 3 sheets, the first sheet is to be an overview of the entire workbook used to keep track of training,
The last sheet (Called Raw_Data) contains various coloumns, the two I am interested in is the training course and the name, as someone completes a course I record what the course was, who did it and date/time of start/finish,
What I would like to do it to count on the front sheet how many times someone has completed a course
Operational Overview
Joe Blogs
Work Pratices
Joe Blogs
Incident Handling Processes
Joe Blogs
Training
Paul Smith
Incident Handling Processes
Joe Blogs
From the above table Job blogs should flag in a cell for Incident Handling Processes twice, Is this something that can be done via a formula as I am trying to avoid VBA.
View 1 Replies
View Related
May 29, 2006
This is a very simple thing that i can't seem to figure out or find the right code in search.
How do i count the number of columns in an excel worksheet?
- No selection should be made
- If it helps, the worksheet only shows the columns and rows with data, if there are no data there are no columns, something about the Page break view...
View 9 Replies
View Related
Apr 15, 2013
I import a text file that displays outstanding parts orders. The order Number column only displays one number even if there are multiple parts ordered I.E. so if three parts are on order then the order number is displayed once with the other two underneath blank. I would like to filter the column that displays the amount of stock that is available so that even if the row has no stock but the other two have all three rows are displayed that reference the order number.
Example Screen
Example.jpg
View 5 Replies
View Related
