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

Automated filling of subsequent sheets based on condition

Pasadu

Member
Dear Sir, Pleae i have sheets that must be filled automatically depending on the conditions on the Master Sheet. I used the if formula but couldnt get it because I want each row to have a unique value. Thank You
 

Attachments

p45cal

Well-Known Member
The attached has tables on each sheet. All except the MASTER SHEET update their table when that sheet is activated, which is done by a 2-line macro.
This macro isn't esssential because each table can be updated by right-clicking on it and choosing Refresh.
Make sure that when you update the MASTER SHEET that the table fully encompasses all your data (this should happen automatically).
 

Attachments

Pasadu

Member
Thank You sir, I really appreciate it. But please, I want the data to have unique values. Meaning Each row should be distinct. If the order number is AMD100, it should be recorded once on the row, it should not be repeated like 2 times or 3 times.
The repeatition is done on the master data, but the other sheets should have just unique extractions, the order numbers should appear ONCE, not repeated overthere. Thank You once again.
 

Attachments

Marc L

Excel Ninja
Attach at least a workbook with the exact expected result worksheets as they must be so without any useless like comment etc …​
 

Pasadu

Member
See attached. Similar to before but this time the macro is required to remove duplicates.
In the cases of PAID and TRANSFERRED sheets you are losing information; the Container# where only the first is retained.
Thank you, it worked perfectly as I desired. Nonetheless, can you guide me in SUMMARY as to how you did it, so I can do same when i come across other workbooks?
Irrespective, thank you for the work, it is Perfect.
 

p45cal

Well-Known Member
I've been silly with that last offering; I removed duplicates right at the end of each refresh using a macro where I should've/could've done it as the last step in the query.
So the same caveats as in my msg#2 above. The macro's still in there but not absolutely required.
How it works:
Power Query. There are 8 queries; 1 'landing' query which grabs the raw data from the Master Sheet, then 7 others (each of which uses the 'landing' query as a source), one for each results sheet which only (a) filter, (b) return a subset of columns and (c) remove duplicates.
Like pivot tables, if the source data changes they need refreshing - the macro does that as each results sheet is activated (moved to from another sheet).
To explore what's happening in Power Query, right-click any of the results tables and choose Table, then choose Edit query…
On the right of the new window you'll see Applied Steps which you can click on in sequence to see what's happening. On the left, you can expand a Queries pane to explore the 8 queries.
78739

Table1 is the landing query, the others are the individual results queries/tables whose names should tell you what they're about.
 

Attachments

Pasadu

Member
I've been silly with that last offering; I removed duplicates right at the end of each refresh using a macro where I should've/could've done it as the last step in the query.
So the same caveats as in my msg#2 above. The macro's still in there but not absolutely required.
How it works:
Power Query. There are 8 queries; 1 'landing' query which grabs the raw data from the Master Sheet, then 7 others (each of which uses the 'landing' query as a source), one for each results sheet which only (a) filter, (b) return a subset of columns and (c) remove duplicates.
Like pivot tables, if the source data changes they need refreshing - the macro does that as each results sheet is activated (moved to from another sheet).
To explore what's happening in Power Query, right-click any of the results tables and choose Table, then choose Edit query…
On the right of the new window you'll see Applied Steps which you can click on in sequence to see what's happening. On the left, you can expand a Queries pane to explore the 8 queries.
View attachment 78739

Table1 is the landing query, the others are the individual results queries/tables whose names should tell you what they're about.
Thank you sir for your time and effort. I will have a look at the table later on a PC. Please how can I contribute to this page if I want to support it with money?
 

Pasadu

Member
Hi Pasadu,

If there's a specific forum member you'd wish to donate to, several have links in their signature, or the Excel Ninjas (volunteer forum mods) have links here: https://chandoo.org/forum/threads/donate-support-our-ninjas.18150/

If you wish to support Chandoo directly, his contact info is here: https://chandoo.org/wp/contact/

Hope this helps.
Sir, could you please have a look at this data for me where there should be automatic feeling of other sheets based on conditions on the Master Sheet? I have attached the file please. Thank You.
 

Attachments

Top