How to input data from a production batch, with a lot of composition

yudeakurnia

New Member
Joined
Jul 9, 2021
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
Hello, I want to input a lot of data from a production batch into Microsoft Excel with version 2019. I want to search for an effective way to do it and to minimize any mistake from inputting at the wrong column, and to make the process a lot faster, because there's a lot of handwritten notes of the production batch for every single day.

(example)
1625985096781.png


The Production batch example is stated below in the mini sheet, there are a lot of products and I need to write the composition of the product, and sum it at the end to see how much is used and to be used as a recap, as you can see that the example composition, not every product have the same composition, therefore, it will take a long long time to input it into the table without any formula or function. I think its like if I put P60:12 or AP:23 or something else it will automatically inserted to the respective column, can I do that kind of input in Excel?


any answer will be appreciated
Thanks
------


Book1
ABCDEFGHI
1DateProductComposition
2P60HVI650AP DPPB57BBB2
301-Jun-21ExampleProduct12423231,234
4ExampleProduct212122323412112
5ExampleProduct312323267
6ExampleProduct4542312
7ExampleProduct51223
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C3:I7Cellcontains a blank value textNO
C7Celldoes not contain a blank value textNO
H7Celldoes not contain a blank value textNO
G7Celldoes not contain a blank value textNO
F7Celldoes not contain a blank value textNO
E7Celldoes not contain a blank value textNO
D7Celldoes not contain a blank value textNO
C3:I6,J4Celldoes not contain a blank value textNO
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
If you are saying that you get batch data in a text file, and you want to store it in the format you've shown in your example, then you need to show us what the batch data looks like.

If instead, you've shown us an example of the batch data, then you need to show us how you want it stored in Excel.

In other words, we need examples of both the data you start with, and what you want it to look like at the end
 
Upvote 0
If you are saying that you get batch data in a text file, and you want to store it in the format you've shown in your example, then you need to show us what the batch data looks like.

If instead, you've shown us an example of the batch data, then you need to show us how you want it stored in Excel.

In other words, we need examples of both the data you start with, and what you want it to look like at the end
Hello! Thanks for replying! I just wanted to see the total amount of the composition at the end. I think I want it to look like on the example I provided with the sum at the end of the month.

I'm sorry but the batch data is handwritten,

I don't want to give the real one, but this is the "mock-up" that I created, the structure is nearly like this: (look on the file attachment)

The order of the composition is also some kind of random.
 

Attachments

  • 1.jpeg
    1.jpeg
    119.8 KB · Views: 13
Upvote 0
Hello! Thanks for replying! I just wanted to see the total amount of the composition at the end. I think I want it to look like on the example I provided with the sum at the end of the month.

I'm sorry but the batch data is handwritten,

I don't want to give the real one, but this is the "mock-up" that I created, the structure is nearly like this: (look on the file attachment)

The order of the composition is also some kind of random.
also don't mind about destination, I don't really use that in the data
 
Upvote 0
Hello! Thanks for replying! I just wanted to see the total amount of the composition at the end. I think I want it to look like on the example I provided with the sum at the end of the month.

I'm sorry but the batch data is handwritten,

I don't want to give the real one, but this is the "mock-up" that I created, the structure is nearly like this: (look on the file attachment)

The order of the composition is also some kind of random.
I want my excel to be like in the example, but I don't want to input it one by one because I'm worried about mistakenly wrote the wrong value in the wrong column.

I ever asked someone on Reddit but it was not pretty clear, can I just write for example:
ExampleProduct6 |AP,12|HVI650,12|P60,24

and split the string one by one, into the respective column,
Thanks
 
Upvote 0
This should work

The attached screenshot shows how you can enter the handwritten data as a single line separated by commas (I've used your first handwritten example)
The split area on the right from col J breaks it into cells, using a custom VBA function
The check cell in col B lets the user confirm the total is correct
Cols C to I look up the ingredients that have been entered, using a formula

So all you need is the custom formula - note this ONLY gets entered in J3 - it will fill k3, L3, etc all by itself
This will work as long as your Excel version is reasonably up to date, otherwise you'll need a different custom function
VBA Code:
Function SplitData(txt, Optional delim = ",")
  SplitData = Split(txt, delim)
End Function
2021-07-11 21_07_14-Window.jpg
 
Upvote 0
This should work

The attached screenshot shows how you can enter the handwritten data as a single line separated by commas (I've used your first handwritten example)
The split area on the right from col J breaks it into cells, using a custom VBA function
The check cell in col B lets the user confirm the total is correct
Cols C to I look up the ingredients that have been entered, using a formula

So all you need is the custom formula - note this ONLY gets entered in J3 - it will fill k3, L3, etc all by itself
This will work as long as your Excel version is reasonably up to date, otherwise you'll need a different custom function
VBA Code:
Function SplitData(txt, Optional delim = ",")
  SplitData = Split(txt, delim)
End Function
View attachment 42617
Thank you for your response, I'm so sorry I'm a newbie, I don't have many experience with excel, to clarify, so the first thing to do is to input the data and separate it by comma. What is the purpose of the split of raw data? After the data is then splitted, how do I enter the value to the respective composition value?
 
Upvote 0
I just wanted to see the total amount of the composition at the end.
The one that i refer is like this,

1626055620592.png


The total sum of the composition is at the end of month (at the bottom of the composition table, Just pretend that the row 9-10 is the end of the month, the total sum will be added using the sum function as usual



I'm sorry but how did you fill the table, and why in column BB, ap is present? is it mistype?

do i need to enter the formula stated for B3 C3 and J3 for the table to be filled?
 
Upvote 0
This should work

The attached screenshot shows how you can enter the handwritten data as a single line separated by commas (I've used your first handwritten example)
The split area on the right from col J breaks it into cells, using a custom VBA function
The check cell in col B lets the user confirm the total is correct
Cols C to I look up the ingredients that have been entered, using a formula

So all you need is the custom formula - note this ONLY gets entered in J3 - it will fill k3, L3, etc all by itself
This will work as long as your Excel version is reasonably up to date, otherwise you'll need a different custom function
VBA Code:
Function SplitData(txt, Optional delim = ",")
  SplitData = Split(txt, delim)
End Function
View attachment 42617
hello? @Dermot
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top