Jan 06 2023 12:15 PM
Hello,
I have a large spreadsheet containing over 6000 rows that I exported from Quickbooks. I want to know how much we spent on a particular event. The name of the event, firm meeting, is included in a description field but it is not always 3 from the left or right and may have other words around it. I tried the filter command but that did not work. I have shown a sample of a few rows below. Please help, doing this manually
7310 Internal Travel Airfare | 06/30/2022 | Bill | 97666114R | Lisa | DELTA 00678235286562 SEATTLE WA - Airfare to Annual Firm meeting in Milwaukee 9-19 - 9-21-22 | 20000 Accounts Payable | 567.21 | |
7310 Internal Travel Airfare | 07/19/2022 | Bill | 96743099R | Alexis | United Airlines - Flight Firm Meeting - PWM - Milwaukee - Sep 19 - Sep 21 | 20000 Accounts Payable | 476.20 | |
7310 Internal Travel Airfare | 08/29/2022 | Bill | 6778522376312819R | Holly | United Airlines - Firm meeting | 20000 Accounts Payable | 298.60 | |
7310 Internal Travel Airfare | 08/29/2022 | Bill | 6778522376312819R | Holly | Delta Airlines - Firm meeting | 20000 Accounts Payable | 187.60 | |
7310 Internal Travel Airfare | 09/12/2022 | Bill | 3589640410366929R | Jane | Delta Airlines - Internal chicago trip & conference | 20000 Accounts Payable | 637.20 |
Jan 06 2023 06:17 PM
Jan 06 2023 06:52 PM
Jan 13 2023 01:08 PM
Here is a sample of the raw data
Date | Transaction Type | Num | Class | Memo/Description | Split | Amount | |
7310 Internal Travel Airfare | 06/30/2022 | Bill | 97666114R | DELTA 00678235286562 SEATTLE WA - Airfare to Annual Firm meeting in Milwaukee 9-19 - 9-21-22 | 20000 Accounts Payable | 567.21 | |
7310 Internal Travel Airfare | 07/19/2022 | Bill | 96743099R | United Airlines - Flight Firm Meeting - PWM - Milwaukee - Sep 19 - Sep 21 | 20000 Accounts Payable | 476.20 | |
7310 Internal Travel Airfare | 08/29/2022 | Bill | 6778522376312819R | United Airlines - Firm meeting | 20000 Accounts Payable | 298.60 | |
7310 Internal Travel Airfare | 08/29/2022 | Bill | 6778522376312819R | Delta Airlines - Firm meeting | 20000 Accounts Payable | 187.60 | |
7310 Internal Travel Airfare | 09/12/2022 | Bill | 3589640410366929R | Delta Airlines - Internal chicago trip & conference | 20000 Accounts Payable | 637.20 | |
10/13/22 | Bill | 6778522376312819R | SW Airlines | 20000 Accounts Payable | 297.82 |
Here is my expected result
7310 Internal Travel Airfare | 06/30/2022 | Bill | 97666114R | DELTA 00678235286562 SEATTLE WA - Airfare to Annual Firm meeting in Milwaukee 9-19 - 9-21-22 | 20000 Accounts Payable | 567.21 | |
7310 Internal Travel Airfare | 07/19/2022 | Bill | 96743099R | United Airlines - Flight Firm Meeting - PWM - Milwaukee - Sep 19 - Sep 21 | 20000 Accounts Payable | 476.20 | |
7310 Internal Travel Airfare | 08/29/2022 | Bill | 6778522376312819R | United Airlines - Firm meeting | 20000 Accounts Payable | 298.60 | |
7310 Internal Travel Airfare | 08/29/2022 | Bill | 6778522376312819R | Delta Airlines - Firm meeting | 20000 Accounts Payable | 187.60 | |
|
Please note the words I am looking for are "firm meeting". There are 956 rows of data that need to be looked through and I only want the rows returned that have firm meeting in them. The purpose is for me to be able to add up all the charges pertaining to the firm meeting.
Susan
Jan 13 2023 01:44 PM
You can try Power Query. In the attached file you can enter data in the blue dynamic table. Then you can click in any cell of the green table and right click with the mouse and select refresh.
The layout of the tables in the screenshot is for illustration. You can place the green table to the right of the blue table or in another worksheet.
If you work with Excel 2019 or later you can apply the FILTER function which would be an easier solution.
=SUMPRODUCT(ISNUMBER(SEARCH("firm meeting",Tabelle2[Spalte5]))*Tabelle2[Spalte7])
You can try this formula if you only want to sum the transactions with "firm meeting".
Jan 13 2023 01:59 PM
Jan 13 2023 03:26 PM - edited Jan 13 2023 03:27 PM
=FILTER(Tabelle2,ISNUMBER(SEARCH(A17,Tabelle2[Spalte5])))