Changing Vertical Data Format to Horizontal

I need help in Excel to get the data with Criteria. Value are written vertically in Excel sheet as like below.

AA
2
BB
4
CC
6
AA
8

Output i want As below like AA and it's value in different column with Max value from master data.
AA 8
BB 4
CC 6

Please help

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done it for you this time.)

Try:

=LET(L,A2:A8,N,A3:A9,A,UNIQUE(FILTER(L,ISTEXT(L))),B,BYROW(A,LAMBDA(x,MAX(FILTER(N,L=x)))),HSTACK(A,B))

Or this if you want the data in order alphabetically:

=LET(L,A2:A8,N,A3:A9,A,UNIQUE(FILTER(L,ISTEXT(L))),B,BYROW(A,LAMBDA(x,MAX(FILTER(N,L=x)))),SORTBY(HSTACK(A,B),A,))

If they need to be sorted, you can also do it inside A:

=LET(L,A2:A8,N,A3:A9,A,SORT(UNIQUE(FILTER(L,ISTEXT(L)))),B,BYROW(A,LAMBDA(x,MAX(FILTER(N,L=x)))),HSTACK(A,B))

same result... a little shorter.

1. Where did you request a LOOKUP?

2. My formula will work, if your sample was realistic.

3. If not, post a REALISTIC sample, in an Excel file.

Just extend the ranges to suit.

So. Your sample was unrealistic. I'm still not sure what you want - as you provided NO expected answers!!

=LET(L,Master!C2:C66,N,Master!C4:C68,A,FILTER(L,ISNUMBER(SEARCH("data",L))),B,BYROW(A,LAMBDA(x,IFERROR(FILTER(N,(L=x)*(N<>0)),""))),HSTACK(A,B))

see file. make sure the area where you want to see the results is EMPTY

In Master sheet in D2:

Formula:

Please Login or Register  to view this content.

And in Report sheet:

Formula:

Please Login or Register  to view this content.

Who are you talking to?

upload a file with EXPECTED results. I don't want to misinterpret you again.

I do not want a non-working formula. Upload a sheet with EXPECTED results.

I have asked several times for expected answers. You have not provided them.

Good luck, but I will not be helping you further.

The combination of lookup functions give you a first occurrence of the item in the list. You asked for max value and that is why I provided you a solution with Maxifs. You need to adjust the range accordingly.
And it works, as you see in the attached file: