Hello Folks,
I am trying to find a formula that can self adjust automatically the range it searches based on the number of blank cells (rows) between 2 sets of data? And if so, can you help me with searching for the text in either the cell alone or within a textstring? Below are the details
For Tax Preparation, I copy and paste quicken reports into my excel spreadsheets. You folks helped me overcome a hurdle I had a few weeks ago, Again my sincere Thanks.
When I paste this report into Excel it creates about 8 columns of data and I have a few thousand rows to review. I am looking for hopefully a shortcut in this review process of mine. The Data I end up with is formatted as follows: The 8 columns are
Col A - Date
Col B - Account
Col C - Num
Col D - Payee
Col E - Memo
Col F - Category
Col G - Tag
Col H - Amount
Each Transaction [purchase at a store] I have, quicken allows you to enter as much or as little details regarding the purchase. In quicken you can enter details about the purchase by creating what quicken calls a split transaction. All this does is allow you to take the $150 grocery purchase and instead of one line transaction, in the split transaction area you can Fill out details [Memo, Category, Tag, Amount] for each item purchased or by grouping of items purchased. Example is a person may split that $150 grocery bill that originally has a one line entry with a category of just “Food: Groceries” into a 5 line entry with sub categories for Dairy, Meat, Sweets, Produce and Sales tax [ie Food:Groceries:Dairy, etc]
In excel each transaction starts with the 1st Row (the Dated Row) where all 8 columns have data entered.
If the transaction is broken down into details, each additional detail row below the 1st “Dated” Row will have data only in the 4 right columns [ Col E thru H]. Colmns A-D in each of the detailed rows will be blank.
So Col A-D only have data when each new Transaction Starts.
At the tax time (now) I paste a Quicken Register Report (all transactions of a specific account ie checking, creditcard etc). If the transaction has the minimum details, it only consists of one row of data. If it has a lot of details, that one transaction could be comprised of 20 rows or more. Where the first row has all 8 columns filled in and the subsequent 19 rows, data is just in the right 4 columns E through H.
My purchases are automatically downloaded into quicken from my bank or credit card company. This creates the single “Dated” row for each downloaded purchase. Then I take my receipts and add details to each transaction [purchase]. This adds additional rows to each transaction.
I need to review all my entries to ensure I have entered the receipt details to each transaction. Specifically I need to ensure I have entered the Category “Sales Tax” of a category textstring that contains “Sales Tax” in one of the detailed rows below the initial “Dated” Row of the transaction. So at a minimum I need to have 2 rows per transaction.
If the receipt has sales tax on it, it will have a minimum of two rows of data [Example]
Col A..............B..................C........D...............E........... F........................G..............H
Date...............Account........Num....Payee.........Memo..... Category..............Tag...........Amount
1/1/11............CreditC....................Starbucks ...Coffee.....Food:Dining:Drink...XYZ...........4.75
.................................................................................Sales Tax.............xyz.............0.33
To verify each transaction has been updated and a sales tax row entered, I need a formula to search for Sales Tax within each transaction. I want to create a formula that will flag OK if the transaction has the minimum details entered (ie the 2nd row or any of the detailed rows below the initial dated transaction row, has “sales tax” under the category in col F, OR “Sales Tax” within the Category Text String [ie medical:Script:Sales Tax]
The same formula will flag an Error if sales tax has not been entered by either
(1) The Transaction is only a single row [ie no additional details (rows) have been added] OR
(2) Within the detail rows below the first (Dated ROW) , a search of Column F cannot find either “Sales Tax” as the category OR “Sales Tax” within the Category Text String [ie medical:Script:Sales Tax] somewhere in one of the extra rows below the 1st (Dated) row of the transaction.
Realizing the number of rows associated with a single transaction can vary between 1 and 40 (or more). The similarities the entries do have are, if details were added then there will be several rows with blank cells in Cols A-H.
So I was hoping there was a way the formula could count the number of col A cells that are blank between the Dated 1st Row of the Transaction and down until the next dated 1st Row of the following Transaction occurs.
That count would help define the search range [ie how many rows] in column F. With that specific range defined for that transaction, the formula searches column F for either “Sales Tax” alone as the category OR “Sales Tax” within the Category Text String [ie medical:Script:Sales Tax].
AND
IF there are NO blank cells between the two dates – in the dated rows of subsequent transactions, that too would flag an Error [meaning I still need to enter receipt data, and enter a row for sales tax]
This report in excel is just shy of 3,000 rows, creating a cell next to the date [move the columns over if necessary] that has a big visual indicating OK or Error would quickly help my locate problems that need addressed. And by always pasting the report in the same cell each year, Formulas can be made permanent on each side of the report and essentially automating the process, where all I have to do is paste the report and then review the existing formula flags for Errors.
With a formula that determines the search area automatically counting blank cells below the date, should work whether the details are minimum yielding only two rows for a transaction or one that is super detailed with information about each item purchased and creating a row for each item purchased [possibly 20 to 30 to 50 or more rows for one transaction], The number of rows doesn’t matter as long as the formula searches all of those rows and Sales Tax is entered at least onc time in one of those transaction detailed rows.
Is there a formula that can self adjust the range it searches based on the number blank cell(s) [ie rows] in-between the transaction in question and the next one in the report?
And what would I use to locate the text within a text string?
A few weeks ago you helped me with the same tax project but I was struggling the formula recognizing the category if it was buried within a text string. You folks gave me this formula using wild cards to help accomplish that. =SUMIF($R$176:$R$796,"*"&AF28&"*",$AA$176:$AA$796)
I am presuming once we can get the formula to determine the range size of where to search a formula similar to this can be combined , though I do not need to sum anything, but instead a yes /no or true / false response based on what it finds.
Summary,
I need to verify Each transaction has an extra detail row for Sales Tax [either as a stand alone category or within a category text-string. In col F]
A transaction starts with the first “Dated” row [all 8 columns has data entered in the first dated row]
Subsequent detailed rows only have data in the right 4 columns [E-H]
The number of detailed rows vary from Zero to 40 plus, or could be missing all together
Sales Tax is never in the first (“Dated”) row, it will always be in a subsequent row, unless I failed to update that transaction.
Is there a formula that can count the number of blank cells between each “Dated” (1st Row) of the transactions. This count will hopefully yield the search range of column F in that specific transaction to find “Sales Tax”
If any of the subsequent transaction detailed rows has “Sales Tax” in column F [alone or as part of a text string]
Then the formula should return an “OK”
If Sales Tax cannot be found in any of the col F cells in the detailed rows OR there are no detailed rows [ie only existing is the 1st dated Row of the transaction ] Then the formula should return an “ERROR”
Thank you so much for your assistance
Sincerely
Keith
I am trying to find a formula that can self adjust automatically the range it searches based on the number of blank cells (rows) between 2 sets of data? And if so, can you help me with searching for the text in either the cell alone or within a textstring? Below are the details
For Tax Preparation, I copy and paste quicken reports into my excel spreadsheets. You folks helped me overcome a hurdle I had a few weeks ago, Again my sincere Thanks.
When I paste this report into Excel it creates about 8 columns of data and I have a few thousand rows to review. I am looking for hopefully a shortcut in this review process of mine. The Data I end up with is formatted as follows: The 8 columns are
Col A - Date
Col B - Account
Col C - Num
Col D - Payee
Col E - Memo
Col F - Category
Col G - Tag
Col H - Amount
Each Transaction [purchase at a store] I have, quicken allows you to enter as much or as little details regarding the purchase. In quicken you can enter details about the purchase by creating what quicken calls a split transaction. All this does is allow you to take the $150 grocery purchase and instead of one line transaction, in the split transaction area you can Fill out details [Memo, Category, Tag, Amount] for each item purchased or by grouping of items purchased. Example is a person may split that $150 grocery bill that originally has a one line entry with a category of just “Food: Groceries” into a 5 line entry with sub categories for Dairy, Meat, Sweets, Produce and Sales tax [ie Food:Groceries:Dairy, etc]
In excel each transaction starts with the 1st Row (the Dated Row) where all 8 columns have data entered.
If the transaction is broken down into details, each additional detail row below the 1st “Dated” Row will have data only in the 4 right columns [ Col E thru H]. Colmns A-D in each of the detailed rows will be blank.
So Col A-D only have data when each new Transaction Starts.
At the tax time (now) I paste a Quicken Register Report (all transactions of a specific account ie checking, creditcard etc). If the transaction has the minimum details, it only consists of one row of data. If it has a lot of details, that one transaction could be comprised of 20 rows or more. Where the first row has all 8 columns filled in and the subsequent 19 rows, data is just in the right 4 columns E through H.
My purchases are automatically downloaded into quicken from my bank or credit card company. This creates the single “Dated” row for each downloaded purchase. Then I take my receipts and add details to each transaction [purchase]. This adds additional rows to each transaction.
I need to review all my entries to ensure I have entered the receipt details to each transaction. Specifically I need to ensure I have entered the Category “Sales Tax” of a category textstring that contains “Sales Tax” in one of the detailed rows below the initial “Dated” Row of the transaction. So at a minimum I need to have 2 rows per transaction.
If the receipt has sales tax on it, it will have a minimum of two rows of data [Example]
Col A..............B..................C........D...............E........... F........................G..............H
Date...............Account........Num....Payee.........Memo..... Category..............Tag...........Amount
1/1/11............CreditC....................Starbucks ...Coffee.....Food:Dining:Drink...XYZ...........4.75
.................................................................................Sales Tax.............xyz.............0.33
To verify each transaction has been updated and a sales tax row entered, I need a formula to search for Sales Tax within each transaction. I want to create a formula that will flag OK if the transaction has the minimum details entered (ie the 2nd row or any of the detailed rows below the initial dated transaction row, has “sales tax” under the category in col F, OR “Sales Tax” within the Category Text String [ie medical:Script:Sales Tax]
The same formula will flag an Error if sales tax has not been entered by either
(1) The Transaction is only a single row [ie no additional details (rows) have been added] OR
(2) Within the detail rows below the first (Dated ROW) , a search of Column F cannot find either “Sales Tax” as the category OR “Sales Tax” within the Category Text String [ie medical:Script:Sales Tax] somewhere in one of the extra rows below the 1st (Dated) row of the transaction.
Realizing the number of rows associated with a single transaction can vary between 1 and 40 (or more). The similarities the entries do have are, if details were added then there will be several rows with blank cells in Cols A-H.
So I was hoping there was a way the formula could count the number of col A cells that are blank between the Dated 1st Row of the Transaction and down until the next dated 1st Row of the following Transaction occurs.
That count would help define the search range [ie how many rows] in column F. With that specific range defined for that transaction, the formula searches column F for either “Sales Tax” alone as the category OR “Sales Tax” within the Category Text String [ie medical:Script:Sales Tax].
AND
IF there are NO blank cells between the two dates – in the dated rows of subsequent transactions, that too would flag an Error [meaning I still need to enter receipt data, and enter a row for sales tax]
This report in excel is just shy of 3,000 rows, creating a cell next to the date [move the columns over if necessary] that has a big visual indicating OK or Error would quickly help my locate problems that need addressed. And by always pasting the report in the same cell each year, Formulas can be made permanent on each side of the report and essentially automating the process, where all I have to do is paste the report and then review the existing formula flags for Errors.
With a formula that determines the search area automatically counting blank cells below the date, should work whether the details are minimum yielding only two rows for a transaction or one that is super detailed with information about each item purchased and creating a row for each item purchased [possibly 20 to 30 to 50 or more rows for one transaction], The number of rows doesn’t matter as long as the formula searches all of those rows and Sales Tax is entered at least onc time in one of those transaction detailed rows.
Is there a formula that can self adjust the range it searches based on the number blank cell(s) [ie rows] in-between the transaction in question and the next one in the report?
And what would I use to locate the text within a text string?
A few weeks ago you helped me with the same tax project but I was struggling the formula recognizing the category if it was buried within a text string. You folks gave me this formula using wild cards to help accomplish that. =SUMIF($R$176:$R$796,"*"&AF28&"*",$AA$176:$AA$796)
I am presuming once we can get the formula to determine the range size of where to search a formula similar to this can be combined , though I do not need to sum anything, but instead a yes /no or true / false response based on what it finds.
Summary,
I need to verify Each transaction has an extra detail row for Sales Tax [either as a stand alone category or within a category text-string. In col F]
A transaction starts with the first “Dated” row [all 8 columns has data entered in the first dated row]
Subsequent detailed rows only have data in the right 4 columns [E-H]
The number of detailed rows vary from Zero to 40 plus, or could be missing all together
Sales Tax is never in the first (“Dated”) row, it will always be in a subsequent row, unless I failed to update that transaction.
Is there a formula that can count the number of blank cells between each “Dated” (1st Row) of the transactions. This count will hopefully yield the search range of column F in that specific transaction to find “Sales Tax”
If any of the subsequent transaction detailed rows has “Sales Tax” in column F [alone or as part of a text string]
Then the formula should return an “OK”
If Sales Tax cannot be found in any of the col F cells in the detailed rows OR there are no detailed rows [ie only existing is the 1st dated Row of the transaction ] Then the formula should return an “ERROR”
Thank you so much for your assistance
Sincerely
Keith
