Clean and convert the excel

Hi All ,

I have an query regarding an excel power query or normal user interface -

I have excel file where i have columns and rows for customer data , now the problem is that excel is not useful for data analysis as the cells are merged for the report based and their are some data which is missing for the data analysis

Can anyone guide me how to clean up the excel and convert those rows data to columns under new column name remove the blanks and nulls.
if any can kindly reply I will add the data.

if any one can also guide how we can make a new excel document using the only the data we need from other excel sheet using the commands like vlookup and xlookup and index match to pull data like unique name like customer field and other data and additional associated data be loaded in row or columns.

thanks

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

Looking now - please update your forum profile as I requested.

This proposal employs numerous formulas.
For Name: =IFERROR(INDEX(B$2:B$14,AGGREGATE(15,6,(ROW(B$2:B$14)-ROW(B$1))/(B$2:B$14<>""),ROWS(A$21:A21))),"")
For loan Amount, Discount, Amount and Status: =IFERROR(INDEX(C$2:C$14,AGGREGATE(15,6,(ROW(C$2:C$14)-ROW(C$1))/(C$1:C$13=B$20),ROWS(B$21:B21))),"")
For Approved Amount, Total Amount, Payment and Amount Received: =IFERROR(INDEX($D$2:$D$14,AGGREGATE(15,6,(ROW(E$2:E$14)-ROW(E$1))/($C$2:$C$14=E$20),ROWS(D$21:D21))),"")
Let us know if you have any questions.