Data Validation Issue and Data Manipulation of Delimited Data on a Cell

Originally Posted by FDibbins

Greetings my young padawan, welcome to the forum and may the excel be with you!

Taking a look at your file, what you want is easily accomplished. However, there is 1 major hurdle to overcome before we can get you where you need to be. The data you have in column J is pretty much impossible to work with for what you want to do.
{"s_Step1":"1","s_Step2":"4","s_Step3":"9","s_Step4":"20","s_Step4_s":"1","s_Step5":"22","s_Step5_s":"1","s_Step5_r":"0","s_Step6":"29","s_Step7":"34","s_Step8":"31","s_Step8_s":"3","s_Step8_r":"2","s_Step9":"37","s_Step9_s":"3","s_Step9_r":"2","s_Step10":"38","s_Step10_s":"3","s_Step10_z":"2","s_Step10_r":"2","s_Step11":"41","s_Step11_s":"1","s_Step11_z":"2","s_Step11_r":"3","s_Step12":"43","s_Step12_s":"1","s_Step12_z":"2","s_Step12_r":"3","s_Step13":"44","s_Step13_s":"3","s_Step13_z":"1","s_Step13_r":"2","s_Step14":"47","s_Step14_s":"3"}
when you do the download, can you at least try and break that down into all of the different steps?. if you can do that, we can use a vlookup, into a table that "explains" all of the codes, and then use index/match to extract the data

for instance...
=INDEX(MergedAccountsProdOrdersProduct!$B$2:$AH$15,MATCH('Job Order Form'!D2,MergedAccountsProdOrdersProduct!T2:T15,0),26)
this will extract the client name

But for most of what you want, you will need to find some way to break up the info in J (and K, for that matter)

I am very thankful for your response, Master Jedi. Regarding the part when you said "when you do the download, can you at least try and break that down into all of the different steps?", do you mean, separating the steps into different columns, right? Super Thanks!

Thank you for showing me the excel force, I will try to delimit or separate the steps during download from the database then I hope that I can still go back to you, Master. I am also thinking of creating a way to delimit the steps into different columns within the actual excel file.

Master, you are correct, we are using "," as the data separator, regarding what it should look like, we could try any temporary data like for "s_Step4_s":"1" = Black or anything, is this what you mean? And then I will be the one to fill up the correct data later on. I am now browsing/researching text-to-columns but if it is fine with you, may I feel the force how did you do it? May the force be with you, master.

I tried it on the column J or on the steps. The problem is that it overwritten the other columns or data on its right part. And also, Whenever I refresh the Data to get new update or new data from the database, It is returning to its original table alignment and data presentation, the Text-to-columns only last during execution but it is not flexible or static to separate the steps into different columns.

Originally Posted by FDibbins

there is 1 major hurdle to overcome before we can get you where you need to be. The data you have in column J is pretty much impossible to work with for what you want to do.
{"s_Step1":"1","s_Step2":"4","s_Step3":"9","s_Step4":"20","s_Step4_s":"1","s_Step5":"22","s_Step5_s":"1","s_Step5_r":"0","s_Step6":"29","s_Step7":"34","s_Step8":"31","s_Step8_s":"3","s_Step8_r":"2","s_Step9":"37","s_Step9_s":"3","s_Step9_r":"2","s_Step10":"38","s_Step10_s":"3","s_Step10_z":"2","s_Step10_r":"2","s_Step11":"41","s_Step11_s":"1","s_Step11_z":"2","s_Step11_r":"3","s_Step12":"43","s_Step12_s":"1","s_Step12_z":"2","s_Step12_r":"3","s_Step13":"44","s_Step13_s":"3","s_Step13_z":"1","s_Step13_r":"2","s_Step14":"47","s_Step14_s":"3"}
when you do the download, can you at least try and break that down into all of the different steps?. if you can do that, we can use a vlookup, into a table that "explains" all of the codes, and then use index/match to extract the data
)

Master, after almost 5 hours of balancing the force, I have managed to follow your advise to distribute the column J to different columns. Please see the new attachment on this post for my new workbook. Like what you have told me, I screened the column during the downloading of the data from the database. Now I am back on my problem 1 and problem 2. Please guide me master on how I can trigger the data validation that whenever a Job Order Number is selected, it will populate the whole Job Order Report with the right data from the columns of another sheet. Thanks

Originally Posted by FDibbins

ok, thats a good start.

Now, I assume that each of those codes represent something. You will need to create a table that shows all the codes, and what they represent

Hi Sir,

I followed your advice, please refer to the attachment for my updated report. I created a new sheet named "Table" for the corresponding value of distributed column or for the "codes represent something". I already included your input regarding the client name which is "=INDEX(MergedAccountsProdOrdersProduct!$B$2:$AH$15,MATCH('Job Order Form'!D2,MergedAccountsProdOrdersProduct!T2:T15,0),26)" and also already modified it for the date. My data validation is now functioning for the date and client name. My only issue remaining is the Problem 2. Thank you and I am hoping for another guide regarding this.

Originally Posted by FDibbins

see the attached, my questions are on sheet2

Hi Sir, Please refer below for my answers to your questions:

1st problem - from the yellow list above, how would you identify which item
goes into which cell in the blue table? Remember, on sheet1, all you have
is 17+ columns of random info. Excel needs to be able to decide what goes where

Answer:
Hi again, sir. I am depending on the Table that I created. Like for example, if the value from the yellow list is "s_Step1":"1", it should go on the Gender. And then before being displayed on the Gender, it should also already be tested or conditioned if what is the equivalent value, if it is "Male" or "Female" - in this example, the equivalent value of "s_Step1":"1" is Female. Same with the Fabric, if the value is "s_Step3":"13", it should be "stretch medium". Please take note of the changes on the value of number preceding the word "Step". If it is Step1 it is for the Gender, if it is Step3, it is for Fabric and so on. Please refer on the attachment for the theory that I am trying to imply here. As you could see, the Data Validation is working fine, and it is also already displaying the correct values on the blue table or on the Gender, Fabric, Shape (Cut) and Wash Type - my only problem is that I don't know how to convert the raw value from the column. I again used your formula for the INDEX here. That is actually almost the same output that I am targeting, the only problem is that it is not yet converted to equivalent which is much readable for the user.

2nd. What about the data in yellow that does not exist in your "equivalent" table?
Answer:
I will also need those data in yellow that does not exist in my equivalent. What I am doing is actually a huge report and as soon as I found out how to convert the values, I am going to continue this on a much larger scale. The file that I am sending you and uploading here from time to time is just an example or part of the report. I will continue it myself once that I found out the conversion. Maybe for now, I will just put a value "Not Yet Available" or something like that.

Thanks, again, sir.

Hi Sir FDibbins, did I my post above answer your question? Thanks.

Sir bebo021999, I have already managed to make everything work. thanks for your help and of master jedi. I will post my finished report here once done.

^sir, thanks. it is now working again. the part that I missed was the CTRL-f3, I did not expect that I also need to do that. THANKS, AGAIN!

Hi Sir,

I am kind of having trouble again with this excel task that I have. My first request doesn't involve two results (two columns) for every referral or query on the index like for example if it is 1302-004, there should be an internal reference to be displayed to the cell beside gender which is women. This time, I need to display to data whenever 1302-004 is being INDEX which is the supplier reference. For example for women, it should be also women but on some other categories it is different.
My problem is that I can’t manage to change your formula to show data on both Internal Reference and Supplier Reference of the Job Order Form sheet whenever 1302-004 is INDEX or selected from the pull down menu.

I also already created a table of values on the Table sheet.

I will appreciate your help again regarding this matter, please refer on the attachment for the half completed excel file.

Thanks.

Hi Guys,

Maybe you could help with my new problem. It's very simple but I don't know why it is not working. I just need to shift the validation of cell c5 (Job Order Form sheet) to column BG of sheet "A". It should be simple but whenever I change it, the validation no longer works. You can still see the JEA-XXX but it can no longer select the right data from sheet "A". I have been fixing this for a week, I hope you guys could help me. Thanks in advanced.

Please see attachment for the actual excel file.

^I will really appreciate any help regarding my problem. Thanks, guys