• 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.

Need to extract only specific fields from data worksheet

shili12

Member
I want to prepare a statement from data worksheet which is 49000 rows,,of course here, i will just give a snippet.
On worksheet called " statement", i want to get intermediary name from dropdown and review entire transactions for the time period defined.
VBA solutions are Ok, too.
 

Attachments

AlanSidman

Well-Known Member
Not a helpful response. What does this mean?
Sorry , but it doesnt seem to work out !
Works in my example for me. Are you trying in a different workbook? I don't know your location, but you may have to change up the date format. I use US convention. You may need to change in the regional settings. I am just guessing here as you have not provided any details of the issue.
 

shili12

Member
All i could see , when i changed the intermediary , the results did not come out as expected.
And i gave out a screenshot to that effect.
Question: Do i need power query installed in my xlsx ?, I attempted it several weeks back but i could not upgrade Windows SP1 in my Windows 7 & as there was a time limit !
I downloaded the file second time, and tried to obtain results for chapters insurance with 3 rows , this is what i obtained.

69482
 

AlanSidman

Well-Known Member
If you are running Excel 2010 or 2013, then you will need to download and add Power Query to your Excel. If you are running 2016, 2019 or 365 then it is already in Excel and is called Get and Transform. If you are running a Mac or 2007, then this option is not available to you.

If PQ is installed, then look at the following to see how it all comes together. Also, you may wish to look at the links in my signature block.

MCode
select your range/table (blue)
Data - From Table
it will open Power Query Editor
find and open Advanced Editor
replace whole code there with code from the post
be sure the name of the source table is the same as in the code (here: Table1)
Done
Close&Load

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps
M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced
 
Top