Hi experts
I have data are not arranged when I use PQ it will make big chaos . so I think VBA could arrange this but with help from the experts .
so I want split the TABLE in TT sheet into multiple tables for multiple sheets based on column A and rename the sheet based on column A for each TABLE .
so TABLE1 =BS Q 4286.pdf start from row2:30 when split will rename the sheet to become is BS Q 4286.pdf when split TABLE should merge columns F,G with column E as in row2,row 25 will see that how merged as in BS Q 4286.pdf. and arrange the columns based on headers should be
column A for BS Q 4286.pdf sheet=column H for TT sheet= ITEM
column B for BS Q 4286.pdf sheet=column E for TT sheet=BRAND
column C BS Q 4286.pdf sheet= column C for TT sheet=ORIGIN
column D BS Q 4286.pdf sheet=column B for TT sheet=QTY
ignore data in columns A,D, also F,G after merging (should merge items for column (G,F with column E)
after split for BS Q 4286.pdf in column B should add to last item by short letters as in adjacent cell for ORIGIN column C which contain words based on matching between two columns in LISS sheet for each adjacent cell
for instance :
if the last item into cell IND for column B and the adjacent cell Indonesia for column C for BS Q 4286.pdf then the last item into cell for column B will become INDO based on matching between two columns in LISS sheet for each adjacent cell
and if the last item into cell doesn't contain short letters then will add based on adjacent cell for ORIGIN column C like JAPAN to become in last item JAP for column B and if the last Item contains short letters like JAP or THI .... in column B then don't add any thing in last item
as to name PR-2023-00001.pdf start from row31:43 when split will rename the sheet to become is PR-2023-00001 and arrange the columns based on headers should be
column A for PR-2023-00001 sheet=column B for TT sheet= Sr
column B for PR-2023-00001sheet=column D for TT sheet=DESCRIPTION
column C for PR-2023-00001= column F for TT sheet=QTY
ignore column A, D, E,G,H
ignore data in columns D,E
column F=QTY , but should delete UNIT word
as to addition to last item by short letters should just add JAP without see LSS sheet because there is no ORIGIN column
as to name STOCK B.S TO 31-10-2022.pdf start from row44:69 when split will rename the sheet to become is STOCK B.S TO 31-10-2022.pdf
the column A for STOCK B.S TO 31-10-2022.pdf=column E for TT sheet =Sr
the column B for STOCK B.S TO 31-10-2022.pdf=column D for TT sheet =Description
the column C for STOCK B.S TO 31-10-2022.pdf=column B for TT sheet =ORIGIN
the column D for STOCK B.S TO 31-10-2022.pdf=column C for TT sheet =QTY
ignore data in columns A,F,G,H
as t0 add short letters follow the same thing as in
BS Q 4286.pdf sheet
should split into multiple sheets & update the sheets have already divided when update TT sheet ,also will add new ranges for tt sheet with the same structure as in attachment .
Bookmarks