danhendo888
Board Regular
- Joined
- Jul 15, 2019
- Messages
- 142
- Office Version
- 365
- Platform
- Windows
I download the below spreadsheet daily from our software. It shows details of invoices paid/booked.
*It doesn't show it below but the first three columns have the little green ribbon on the top left corner of the cell and is aligned to the left of the cell and has General formatting.
Below is my own spreadsheet. I type in the invoice number and retrieve the Order Number, Order Total, and Payment Date from the downloaded file.
The first column has the little green ribbon at the top left corner also.
The last three columns is the output I wish to achieve
Order Number:
To retrieve the Order Number, I use FILTER and UNIQUE. It retrieves it successfully when the invoice number in my spreadsheet has text formatting e.g. ABC123
But it doesn't work when the invoice number in my spreadsheet has number formatting because the invoice numbers in the downloaded file does not have number formatting. How can I make the formula work for both text and numbers.
Also, how can I retrieve the correct order number when the correct invoice number is "ABC123" but it was incorrectly booked as "ABC123 y"
Order Total:
I use two SUMIFS to add the Order Tax and Order Subtotal columns together.
Is there a more efficient way to add these? Or is this okay to use.
Payment Date:
How can I retrieve the payment date for invoice ABC123? The date does not necessarily show in all rows in the extracted data
*It doesn't show it below but the first three columns have the little green ribbon on the top left corner of the cell and is aligned to the left of the cell and has General formatting.
| Company Number | Invoice Number | Order Number | Order Date | Order Tax | Order Subtotal | Currency | Payment Date |
| 1111111111 | ABC123 y | 8190090120 | 3/12/2024 | 10.00 | 100.00 | AUD | |
| 1111111111 | ABC123 y | 8190090120 | 3/12/2024 | 50.00 | 500.00 | AUD | |
| 1111111111 | ABC123 y | 8190090120 | 3/12/2024 | 0.00 | 0.00 | AUD | 3/14/2024 |
| 1111111111 | ABC123 y | 8190090120 | 3/12/2024 | 0.00 | 0.00 | AUD | 3/14/2024 |
| 2222222222 | 111222000 | 8190090000 | 1/1/2024 | 0.00 | 10,000.00 | AUD | |
Below is my own spreadsheet. I type in the invoice number and retrieve the Order Number, Order Total, and Payment Date from the downloaded file.
The first column has the little green ribbon at the top left corner also.
The last three columns is the output I wish to achieve
| Company Number | Invoice Number | Order Number | Order Total | Payment Date |
| 1111111111 | ABC123 | 8190090120 | 660 | 3/14/2024 |
| 2222222222 | 111222000 | 8190090000 | 10,000.00 | |
Order Number:
To retrieve the Order Number, I use FILTER and UNIQUE. It retrieves it successfully when the invoice number in my spreadsheet has text formatting e.g. ABC123
But it doesn't work when the invoice number in my spreadsheet has number formatting because the invoice numbers in the downloaded file does not have number formatting. How can I make the formula work for both text and numbers.
Also, how can I retrieve the correct order number when the correct invoice number is "ABC123" but it was incorrectly booked as "ABC123 y"
Order Total:
I use two SUMIFS to add the Order Tax and Order Subtotal columns together.
Is there a more efficient way to add these? Or is this okay to use.
Payment Date:
How can I retrieve the payment date for invoice ABC123? The date does not necessarily show in all rows in the extracted data
. Then we can look for the lowest non-blank cell in column B, assign that row number to "lrowm"...last row mine, and create a dynamically formed array of your inputs, called "m". These assignments are done first thing in the formula, and should be the only part that needs to be adapted to your workbook. Everything below this in the formula is drawn from these arrays.
