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

All rows to be copy in new worksheet based on predefine multiple criteria

kuldeepjainesl

kuldeepjainesl

Member
Hi Experts,


My another nightmare:


I have one row for each transaction. For example for 2000 customers I have may have 100,000 rows. I need to get all the rows corresponding to a customer name rather I should say (alphanumeric string)between specific dates.


Example Data set

[pre]
Code: 
--------"A"------------------~~~~~~~~~~~~~~~~~~-----"E"-----~~~~~~~~----"G:AZ"----

U.P. JAL VIDYUT NIGAM LIMITED---------------------25/5/2006---------other details
Western Power Distribution------------------------25/9/2007---------other details
TRIPURA STATE ELECTRICITY CORPORATION LIMITED-----4/8/2006----------other details
Scottish Power plc--------------------------------12/8/2009---------other details
Sensus USA----------------------------------------1/12/2010---------other details
Siemens AG----------------------------------------5/8/2011----------other details
SPECTRUM ENGINEERING CONSORTIUM LIMITED-----------9/6/2010----------other details
SSE plc-------------------------------------------6/2/2008----------other details
Swiss Electric Representation Ltd-----------------29/2/2012---------other details
[/pre]
I need, when I put a "Name" in a predefine cell, all the row matching with name should be copy to another worksheet which can be used further. Needless to say that I will give the date input as well in predefined cells.


With above requirement I may need to check two condition (Name & Customer code) along with dates.


Regards,

Kuldeep
 
You could easily do this with an Advanced Filter, which can handle multiple criteria and can copy to a new location.

http://www.contextures.com/xladvfilter01.html
 
Hi Kuldeep ,


I can certainly help out in this , but without a sample workbook , I am not able to fully understand your requirement ; can you upload your file ?


Narayan
 
Dear Kuldeep,


As per my point of view, you can use VLOOKUP or INDEX/MATCH Combination. You can use a helper column becoz you have two criterias Name & customer code. In a helper column you can use A1&B1 (i assumed A1 vendor name & B1 cust code). Now as per your requirement you can use VLOOKUP or MATCH/INDEX COMBINATION


Vijay
 
Hi NarayanK,


Please find the file at


http://sdrv.ms/OtdEHG


or


https://skydrive.live.com/redir?resid=378E9887A5707797!142


Looking forward to get the solution.


Regards,

Kuldeep
 
Kuldeep


You may also want to look at the technique Luke has described here: http://chandoo.org/wp/2011/11/18/formula-forensics-003/
 
Sure NarayanK,


Take your time....You are under no obligation. Let me admit that i have very much respect for you guys as there is no commercial benefit you are getting from us and you guys response is so fast/accurate/amazing that we even do not expect this against paid consultation.


I have corrected few formula in file and the link is http://sdrv.ms/NC2tHS


Regards,

Kuldeep
 
Hi, kuldeepjainesl!


I'm looking at your uploaded SEARCH_NAMES_Updated.xlsm and at the start of this topic, and neither the referred worksheet "Raw data" nor the others (Dashboard, Data Processing, Customer Master and Filtered data) respond to the data structure descripted upwards: A name, E date, G:Z other date.


Am I missing something?


Regards!
 
Hi SirJB7,


Actually i started question just for the one portion of my data architecture but when narayank asked a bit more information i uploaded the file with details embed in it. Please downlode file to understand it.


Oh, I just realize that you have already downloaded the workbook and therefore you mentioned the worksheet names.


Is there any question to be answered on the information, I put in sheet named "Dashboard"?


Regards,

Kuldeep
 
Hi SirJB7,


Further to this :


Response to the data structure descripted upwards: A name, E date, G:Z other date.


1) A is corresponds to the D coloum of raw data. this can be any other coloum as well. For me it is a coloum to be lookup.


2) E is corresponds to the B coloum of raw data. This is the date when transaction was completed in past.


3) G:AZ corresponds to the remaining coloum of raw data.


In basic, the requirement is:


Get the value from "Dashboard" A21,D1,E1,G1

Check for the true condition in "Raw data"

All the true condition rows to be copied in "filtered data"

Filtered data will be map to "data processing" I will do it after getting this solution.

Finally a matrix will be populated as shown on dashboard which will be used as a input to my interactive/dynamic charts.


Hope I could make it a bit clear.


Regards,

Kuldeep
 
Hi Kuldeep ,


Check out the file at this link :


https://docs.google.com/open?id=0B0KMpuzr3MTVbmlLSHJVcWNNSGs


At present , the pasting is always done starting from cell A2 i.e. the second row on the tab labelled "Filtered data" ; any earlier data in this range will be overwritten. If you want that data should be appended to any earlier data , please let me know.


Narayan
 
Hi Narayan,


Something went wrong....:(


Will update you in half an hour....Seems that the user form to source the input is also missing from sheet (user form). Can you please check whether it is present in your sheet?


Regards,

Kuldeep
 
Hi NarayanK,


Please downlode file from....

https://skydrive.live.com/view.aspx?resid=378E9887A5707797!147#!/?id=378E9887A5707797%21147&cid=378e9887a5707797


As i think you downloaded the xlsx version not Xlsm.


Anyway come to your file.


When i run code i get the error "400". and its get terminate.


And for the query of append data....i am surprised how could you know that my ultimate aim will that only. I was planning this to do manually by copying data. Anyway if this can be done please suggest and let this option be configurable for append and overwrite.


Regards,

Kuldeep
 
Hi Kuldeep ,


Sorry about the error. Try this version :


https://docs.google.com/open?id=0B0KMpuzr3MTVUW56SXVTdmg3ckU


Narayan
 
Hi Narayan,


Thanks,


In initial, its running now and copying & appending data....


Observation:


1) If value in additional criteria is blank, it comes out with copying data. This condition i may or may not have..... i did not check for others condition.


2) How i can configure this to overwrite data.....( in case append in not desired)


3) When i tried to club both the solution.... This one and http://chandoo.org/forums/topic/dynamic-search-bar-in-excel-from-partial-input


I get the same error "400"....and in your file this dynamic bar code is missing while it is available in the file i uploaded.


I understand that this is becoming lengthy but i hope you will be able help me.


Regards,

Kuldeep
 
I could remove the code looking for additional condition.....:) but i think it will not be a good idea to play around the again and again....


So the requirement/request is


1) If value in additional criteria is blank, Treat it don't care in case of blank.


2) How i can configure this to overwrite data.....( in case append in not desired)


3) When i tried to club both the solution.... This one and http://chandoo.org/forums/topic/dynamic-search-bar-in-excel-from-partial-input


Regards,

Kuldeep
 
Hi Kuldeep ,


Can you check out the file here ?


https://docs.google.com/open?id=0B0KMpuzr3MTVWkhxTWVPUGltTHM


1. The SEARCH button is on the Customer Master tab.


2. The FILTER DATA button is on the Dashboard tab.


You can relocate these to wherever you want them.


The APPEND / OVERWRITE choice is given , once you click on the FILTER DATA button.


Let me know if you need any other change.


Narayan
 
Thanks NarayanK


So far so good....going to implement in a way I have thought. I am sure that few minor observation will be there to improve upon...I will upload my workbook after implementation...Now it may take time...


Regards,

Kuldeep
 
Hi NarayanK,


By today evening i could build the all basic block of my workbook. All thanks goes to you and Luke M for earlier solutions. Without you this would not have been possible for me.


I just realize that recently posted question at http://chandoo.org/forums/topic/macro-needed-to-remove-rows-based-on-predefined-condition could have been avoided with a minor change in your code....


Can you please suggest change in filter data button code so that beside copying the data, this will cut and paste as we are doing now.


The difference in this case would be only that while we check the input in A21 and copy data based on that, we will cut them and paste and in case of blank (which is not taken care as of now), this time we will cut/paste row for the instance it find any blank cell in predefined coloum.


in case i am not clear with my requirement, please let me know


Regards,

Kuldeep
 
Hi Kuldeep ,


Sorry , but I am not able to understand your requirement ; can you clarify ?


At present , you have the data to be searched in A21 ; you have other criteria in other cells viz. the range of dates in D21 and E21 , and an additional criterion in G21.


When you press the FILTER DATA button , it looks at these cells to carry out the filter action.


What change do you want in this procedure ?


Narayan
 
You must log in or register to reply here.
Back
Top