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

[yudeakurnia]

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)

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
	A	B	C	D	E	F	G	H	I
1	Date	Product	Composition
2			P60	HVI650	AP	DPP	B57	BB	B2
3	01-Jun-21	ExampleProduct1	24		23		23	1,2	34
4		ExampleProduct2	12	12	23	234	121		12
5		ExampleProduct3	12		32			32	67
6		ExampleProduct4	54			23			12
7		ExampleProduct5		12				23
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:I7	Cell	contains a blank value	text	NO
C7	Cell	does not contain a blank value	text	NO
H7	Cell	does not contain a blank value	text	NO
G7	Cell	does not contain a blank value	text	NO
F7	Cell	does not contain a blank value	text	NO
E7	Cell	does not contain a blank value	text	NO
D7	Cell	does not contain a blank value	text	NO
C3:I6,J4	Cell	does not contain a blank value	text	NO

 

[Dermot]

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

 

[yudeakurnia]

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.

 

[yudeakurnia]

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

 

[yudeakurnia]

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

 

[Dermot]

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

Function SplitData(txt, Optional delim = ",")
  SplitData = Split(txt, delim)
End Function

 

[yudeakurnia]

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

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?

 

[yudeakurnia]

I just wanted to see the total amount of the composition at the end.

The one that i refer is like this,

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?

 

[yudeakurnia]

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

Function SplitData(txt, Optional delim = ",")
  SplitData = Split(txt, delim)
End Function

View attachment 42617

hello? @Dermot