dear experts
I am finding it difficult to find a solution for my below problem
My boss assigned me a task to do some automation in excel, and I am struggling to get it done.
Criteria 1
I have a workbook with the sheet "import". There is another sheet named "location" , there are multiple columns in this sheet.
I need to look up the value from "location" sheet column "C", and then check in the "import" sheet column "A", and if it matches, i need to generate a new sheet with the sheet name as column "C" from "location" sheet.
for every change in cell value I should be able to able to generate new sheet
for example, if there is the cell value "DEHAM", i will have sheet named "DEHAM" generated, if there is a cell value "DEBRV", then i will have sheet named "DEBRV" generated
Criteria 2
After generating these new sheets, i need to copy data from sheet named "Import" to these newly generated sheets.
But at the time of copying, i need to check some more criteria
I need to copy data from import sheet multiple times, to these newly generated sheet based on the condition that , if the sheet created is from the cell value in sheet "location" from column "C", e.g., "DEHAM", then the corresponding column "B", for the cell value in "C",should be considered, while copying the full sheet from "Import" to these newly generated sheet e.g."DEHAM".
I mean to say that, the column "B" should get the value of column "B" in the sheet "location", and autofill till the last non blank cell, as for other columns in these newly generated sheet
Then again look up to the cell value in the column "B" in the "location" sheet, corresponding to column "C" in "location" sheet, and then copy the same data from "import" sheet again below the previously copied data in these newly generated sheet.
It should also consider the column "E" in the sheet "Location" which should be copied to the column "I" in the newly generated sheet multiple times, based on the combination between column "C","B", & "E"
for example,
If the sheet generated is DEHAM, then cell value corresponding to this values are "DEHAMTBURC" and "DEHAMTEURC" and also the column corresponding to this is in column "E", which 20s for this group and also 40s for this group.
then in the newly generated sheet, when it is copying from "import" sheet, it will copy the same data from this sheet to newly generated sheet , once for each of these combinations
DEHAMTBURC - 20s
DEHAMTEURC - 40s
Also when the sheet is generated for cell value with different data from "import" sheet to the newly generated sheet , the "AL" column in the newly generated sheet should have ID autogenerated in the format "00001"
for example, for "DEHAM", it will generate the autogenerated ID from "00001" to the last non blank cell
for "DEBRV", when it generates new sheet, then it will generate ID after the last value of the sheet "DEHAM" i.e., if the cell value ends in "DEHAM" sheet "11661", then for "DEBRV" sheet it should start from "11662" onwards. Also once both these sheets are deleted, the ID will be reset and starts again from "00001"
kindly refer to my sample sheet attachments
thanks for your urgent help as i need to submit it by sunday
please help me
regards
MN
Bookmarks