Collating data from a weird data entry form

[Supervaluemeal]

Hi Guys,
I'm new here. I've seen the posts and am confident that I'll find my solution here.

I've been tasked to translate data from user inputs into a table.

The user inputs are fixed and cannot be changed (Because it will disrupt the users workflow)

To ask.JPG

The bigger green box is a drop-down lists. The Red boxes are user inputs. This data entry form is a way for us to track defect types from our raw materials.

How it works
Our guys would enter the material number and the batch number at the left-most red rectangle,
then they would choose, from a drop-down list, the material type (Big green box) which would dictate the defect type that are available to them (small green box & also a drop)
And then they would enter the quantity of defected raw material that share the same defect at the bottom (red rectangle)


My task
I need to break it down on another tab so that it looks like this:

End Goal.JPG

Its something along the lines of transposing, but not quite as its not a perfect array.

I'm not sure of SUMPRODUCT would do the trick and my only experience with VBA is the record function...

Any help would be greatly greatly appreciated!

[TMS]

Looks like a VBA solution is required here. Should be relatively straightforward.


Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Supervaluemeal]

Thanks a lot for offering to help! :D

I've uploaded a desensitized file, it has macro in it already!


Yeah, I figured that I will not be able to get around this issue without the use of Macros.

[TMS]

OK, try this. Copy and paste into a standard module.

Please Login or Register  to view this content.

[Supervaluemeal]

My gosh! It works!! thanks a million!!

I will be slowly studying this VBA code....

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.