• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Extraction

V

vardha.raju

New Member
Hi All,

I need your help in extracting data from sheet1 based on the PBNO column in VD-Sales sheet in the attached excel file. Please help me in solving my problem by providing your solution or valuable guidance.

Varadha Raju K
 

Attachments

  • PARADISE COLLECTION JAN (1).xlsx
    607.2 KB · Views: 7
Last edited by a moderator:
I tried with vlookup but it is returning only single value but PBNo has multiple values in sheet1 I require all such values
 
I do not see a PBNO field and definitely no sheet called VD-Sales. So what data do you wish to extract and what do you want the result to look like. Take a few rows of your data and show us a mocked up solution. My crystal ball is out for repairs, so I have no idea what you are trying to do because you have not explained your issue clearly.
 
I tried to use the variant of vlookup but all values are coming in one cell. Please check in Transformed data tab columns w,x and y. Even i used pivot table also which is placed in sheet 4. Please help me to over come the problem. herewith i am attaching the file.
 

Attachments

  • PARADISE COLLECTION JAN (1).xlsx
    630.6 KB · Views: 0
AlanSidman said:
I do not see a PBNO field and definitely no sheet called VD-Sales. So what data do you wish to extract and what do you want the result to look like. Take a few rows of your data and show us a mocked up solution. My crystal ball is out for repairs, so I have no idea what you are trying to do because you have not explained your issue clearly.
Click to expand...
Hi AlanSidman,
Thanks for your reply. Herewith i am attaching the solution file. I need data from Sheet1 based on the column containing PBNO in VD-Sales. I need to extract Receipt date, Bank Amount, Year, Member Name and Plot No from sheet 1 at the same time i nee the PBNO, Registration date and Sale deed column values from VD-Sales tab. All in one sheet.
 

Attachments

  • PARADISE COLLECTION JAN (1).xlsx
    620.3 KB · Views: 5
Is the table on the Transformed Data sheet of the attached the sort of thing you're looking for?
 

Attachments

  • Chandoo56049PARADISE COLLECTION JAN (1).xlsx
    414.6 KB · Views: 6
Yes but the details are required for the PBNo written in VD Sales tab not all the PBNos as shown in transfored data tab
 
All PBNos in the VD Sales tab are present in the Transformed Data tab. If this is not true, you'll need to give me some examples of missing ones.
Do you want all Pass book numbers from Sheet1 also to be visible in the Transformed Data tab? They'll be missing some columns because they're absent from the VD Sales tab.
 
Last edited:
I need data from sheet1 tab based on PBNo field in VD Sales Tab by establishing a relationship for extracting data from sheet1
 
That is what I've done.
The first 3 columns of the result table (PBNO, REGISTRAION DATE & sale deed) are from VD-Sales, while the next 6 columns (PassBook, RecptDate, year, MemberName, PlotNo, BankAmount) are from Sheet1. If there are multiple lines on Sheet1, for a given PBNO on VD-Sales, they're all shown.
 
Last edited:
Thanks for your help.
Can you explain me the procedure adopted in achieving the end result. Is it through VB or Power pivot which method you have adopted. Because I need to do the same activity again and again.
Thanks once again for your prompt response and solving my problem.
 
vardha.raju said:
Is it through VB or Power pivot which method you have adopted.
Click to expand...
It is Power Query.
If you click on Queries & Connections button in the Queries & Connections section of the Data tab of the ribbon,

1707086066313.png

you will see a panel on the side with 3 queries:

1707086151092.png

The one called VDSales picks up the data from the table on VD-Sales tab and tidies it up a bit.
The one called Table1 picks up the data from the table on Sheet1, and does similar.
The one called Merge1 joins these two other tables then removes the columns you don't want to see, and puts the result on the Transformed Data sheet.
If you change the data on either/both of the tables on sheets VD-Sales and Sheet1, you need to refresh the results on the Transformed Data sheet by right-clicking somewhere in that table and choosing Refresh, just like you would with a pivot table.

You can see what's going on if you:

1707086959470.png

when on the right you will see the steps used in the queries.:

1707087139570.png
 
p45cal said:
It is Power Query.
If you click on Queries & Connections button in the Queries & Connections section of the Data tab of the ribbon,

View attachment 86385

you will see a panel on the side with 3 queries:

View attachment 86386

The one called VDSales picks up the data from the table on VD-Sales tab and tidies it up a bit.
The one called Table1 picks up the data from the table on Sheet1, and does similar.
The one called Merge1 joins these two other tables then removes the columns you don't want to see, and puts the result on the Transformed Data sheet.
If you change the data on either/both of the tables on sheets VD-Sales and Sheet1, you need to refresh the results on the Transformed Data sheet by right-clicking somewhere in that table and choosing Refresh, just like you would with a pivot table.

You can see what's going on if you:

View attachment 86387

when on the right you will see the steps used in the queries.:

View attachment 86388
Click to expand...
Thank you Very much sir your invaluable help to me. Thanks a lot once again.
 
You must log in or register to reply here.
Back
Top