In order to facilitate office work, I believe many friends will use Excel software. So in the Excel table, if you want to extract relevant records based on conditions, how should you operate it? Detailed steps are explained below.
1. Analyze the data table: By browsing the "warehousing details" table, we may see that in the warehousing details table, the part number used as a condition for extracting records is in column A; for the records that need to be extracted, the warehousing date is in column H, warehousing The order number is in column O, the last production batch number is in column L, and the inventory quantity before warehousing is in column Q. There are 5 records for DC000496ZL (the 4 records in the screenshot refer to the 4 records above).
2. List the extraction conditions and items: In sheet1, place the extraction conditions (i.e. part number) in column A. In columns B, C, D, and E, write the names of the extraction items respectively: storage date, storage order number, last production batch number, and inventory quantity before storage.
3. Write a formula: Enter the formula in B2 under the last warehousing date item: =MAX((warehousing details!$A$2:$A$26=$A2)*(warehousing details!$H$2:$H$26) ), this is an array formula, please confirm with three keys (ctrl+shift+enter).
4. Enter the formula = "RK"&LOOKUP(9^323,(SUBSTITUTE(warehousing details!$O$2:$O$1046,"RK",)+0)) in C2 under the last warehousing order number item, ( Because the tracking number is a text type, and the prefix of the tracking number is RK).
5. Enter the formula =MAX((warehousing details!$A$2:$A$26=$A2)*(warehousing details!$L$2:$L$26)) in D2 under the last production batch number item. This is also a For array formulas, please confirm with three keys (ctrl+shift+enter).
6. Enter the formula =MAX((warehousing details!$A$2:$A$26=$A2)*(warehousing details!$Q$2:$Q$26)) in E2 under the final inventory item before warehousing. This is It is also an array formula, please confirm with three keys (ctrl+shift+enter).
The above is the tutorial brought by the editor for you to extract relevant records based on conditions from the data in the Excel table. Let’s learn together. I believe it can help some new users.
