First file Text/excel file to be updated using Second excel file as a reference

Kumar gabani 1 Reputation point
2021-04-04T03:42:54.253+00:00

Hi Experts,

I have one file which is in Text format. This text file can be opened in Excel as well. I have requirement of updating this text file using another Excel file called BOM Excel file.

Now this BOM excel file needs some steps performed on it before we take it as a reference lookup table. BOM excel file has 4-5 columns. There are lets say 3 columns A, B, & C in BOM excel file which are important to be considered as lookup table for First Text/Excel file to be updated.

Following steps need to be performed on BOM excel file to be considered as lookup table:

1st step: If Column A has word "DNP" mentioned in any row(Example: Row 3) then column C data (XXXX-XXX) in Row.3 need to be replaced by word DNP. If Column A is Row.3 is blank then no replacement in Column C Row3 needed. This needs to apply to All Rows, Column A and Column C.

2nd step: Now column B has data like C1 C2 C10 C3 C30 and they are space separated. Each data C1, C2 , C10 will be assigned part number available in Columns C which was update in step.1. Example: If Column C had data 12345-678 then C1 will be assigned 12345-678, C2 will be assigned 12345-678 and same is true for whatever data mentioned in Column B.

Cosidering this assignement done above, we need to update the Text file which will be considered as final file.

Now each data(Example : C1) in column B will be available in Text file with prefix "F9 and space" i.e. F9 C1
we need to update F9 C1 assignement in Text file.
Now data assigned to C1 in text file will be in the 2 row minus last 10 digits. If F9 C1 is on Row.10 then Row.8 will have last 10 digit will have current part number XXXXX-XXX will be updated with 12345-678.

The reason why i need to update text file is I have 70-150 excel files that i will have to take as a reference and update same text file using those 70-150 excel file. this way i will have 70-150 text files as a output.

I hope i have explained the question/project clearly.

I am very frustrated without help so far. Hope i will get help over here.

Please advise best tool Powercell, Macro, VBA, Python and some contact information who can help me finish this project successfully.

If there are any coding ideas then appreciate that as well.

Thanks very much in advance!

If there are any additional question then please let me know.