Want to get the results of mavb for invoices and counts and sorted smallest to largest

[kisiji]

Dear All,

kindly find the attachment EXPORT DETAIL MS test.xlsx

in sheet (mawb) i need results from sheet (D)

i received mawb(master air way bills) files from courier company
now i have data in sheet D
i want all data to fetch from sheet D and if i type the mawb numbers only.

waiting for your kind reply.

[kisiji]

Dear All,

can any 1 tell me how can i use power query ?

waiting for your kind reply.

[JeteMc]

Is the end goal to list the file numbers from smallest to largest for the listed MAWB's?
Otherwise, I don't understand how that column relates to the others, since for example file number 405 is not one of the file numbers for MAWB 092-2112-2452.

[JohnTopley]

The sorted numbers to not relate the files in their rows: they are simply a sorted list from Column D entries

[JeteMc]

True.
Guess that my question should have been are columns B:E necessary, or does the OP just need the results shown in column F?

[kisiji]

Dear All,

i will type the master numbers i am receiving from courier company in B only ,

and i want to fetch all data
C, D, E, F columns automatically.

[kisiji]

dear all,

Either E counts the commas in C or D or total file numbers,
i have to print total 4 times of b4
i have to print total 2 times of b6
i have to print total 1 times of b2

[JeteMc]

This proposal employs helper columns which may be moved and/or hidden for aesthetic purposes.
On the D sheet columns AT:AU are populated using: =IF(Table4[@MAWB]="","",Table4[@[INVOICE No]]&IFERROR(", "&INDEX(AT$1:AT1,AGGREGATE(14,6,(ROW(Table4[@MAWB])-ROW(Table4[#Headers]))/(AD$1:AD1=AD2),1)),""))
On the mawb sheet:
Column C is populated using: =IFERROR(INDEX(D!AT$2:AT$142,AGGREGATE(14,6,(ROW(D!AT$2:AT$142)-ROW(D!AT$1))/(D!$AD$2:$AD$142=$B22),1)),"")
Column D is populated using: =IFERROR(INDEX(D!AU$2:AU$142,AGGREGATE(14,6,(ROW(D!AU$2:AU$142)-ROW(D!AU$1))/(D!$AD$2:$AD$142=$B22),1)),"")
Column E is populated using: =IF(D22="",0,LEN(D22)-LEN(SUBSTITUTE(D22,",",""))+1)
Column F is populated using: =IFERROR(AGGREGATE(15,6,G$22:J$27/(G$22:J$27<>""),ROWS(F$22:F22)),"")
Note that column F pulls values from columns G:J which are populated using: =IFERROR(TRIM(MID(SUBSTITUTE(","&$D22&" ",",",REPT(" ",255)),(COLUMNS($G$22:G$22))*255,255))/1,"")
Let us know if you have any questions.

[kisiji]

Dear JeteMc


Thank you very much for solving my very big problem
a bundle of thanks sir g
hands up for you

[kisiji]

but unfortunately after applying these all formulas my excel file is now corrupt it doesn't work any more as i had total filled cells were A108 till bc20001 and my excel sheet died

[JeteMc]

Perhaps the problem is with the formulas in columns AT:AU on the D sheet.
They have been replaced using tables produced by Get & Transform (aka Power Query) as modeled in columns A:E on the New mawb sheet.
The Advanced Editor code for each table is similar to:

Please Login or Register  to view this content.

The File column is now produced using: =IFERROR(INDEX(AllInvoiceNoTable[All Invoice No],MATCH(J2,AllInvoiceNoTable[MAWB],0)),"")
File No: =IFERROR(INDEX(AllNumTable[All Num],MATCH(J2,AllInvoiceNoTable[MAWB],0)),"")
The File Counts and Smallest to Largest columns use the same formulas as before with the references changed.
Also the helper columns highlighted green utilize the same formula as before.
Let us know if you have any questions.

[JeteMc]

I believe that this will be an improvement over the previous proposal.
A connection only is produced to the two tables from post #11.
The two tables are then merged (JoinKind.FullOuter).
Let us know if you have any questions.

[JeteMc]

I had tried to upload the Export Detail BI file but even zipped at 2.5 MB it will not upload, so I will try to describe how to apply the Power Query based solution:
1. Select a cell in Table_1 on the D sheet
2. From the Data tab select From Table/Range
3. In the Power Query Editor select Advanced Editor and replace the code with the following:

Please Login or Register  to view this content.

4. Select Done
5. Select Close and Load to then Connection only and then OK
6. Repeat steps 1 and 2
7. Replace the Advanced Editor code with:

Please Login or Register  to view this content.

8. Repeat steps 4 and 5
9. From the Data tab select Get Data then From Other Sources and then Blank Query
10. In the Power Query Editor select Advanced Editor and replace the code with the following:

Please Login or Register  to view this content.

11. Select Done
12. Select Close and Load to and then OK (as Table and New Sheet should already be selected)
13. Type MAWB in cell F1 and the #'s in F2 and down
14. Paste the following into cell G2 then drag across to H2 and then down as far as needed:

Please Login or Register  to view this content.

15. Paste the following into cell I2 and drag down as far as needed:

Please Login or Register  to view this content.

16. Paste the following into cell J2 and drag down as far as needed:

Please Login or Register  to view this content.

17. Paste the following into cell K2 then drag down as far as needed and then drag across until there is a blank column:

Please Login or Register  to view this content.

Note that the range of columns in step 17 may have to be extended across further then column O and if so the formula in step 16 will need to be adjusted accordingly.
Let us know if you have any questions.