How to transpose multiple columns and multiple rows in a sheet to another sheet

image1.jpgimage2.jpgimage3.jpgimage4.jpg

dear experts

I would require your help for fixing my issue related to writing a macro to transpose some multiple columns and multipel rows to a summary format in another sheet.
I am attaching all the cases for your immediate response and help.

attached also the excel sheet to work on.

there are three sheets in excelimage1.jpgimage2.jpgimage3.jpgimage4.jpgsmple excel.xlsx

kindly provide me with the right to use macro, because i have been scratching my head for this since last 3 days, and i need to submit it by Sunday,UAE

thanks in advance for the help
regards
MN

hello Mike
that's really great; but just a mistake what I did here, at the time of sending snap shot is I put ID as consecutive; the format shown should have been as attached in the image.img1.jpgimg2.jpg


thanks for the help you have given me; just could you please kindly modify the code to get the same format as attached
thanks
MN

dear Mike
thanks a lot for the reply. I tried to modify a bit of your code, by adding one more counter as below; and then it got sorted out.
Option Explicit
Sub populate()
Const cShRawData As String = "Import Ham"
Const cShOutPutData As String = "Wt. Slab Data"
Dim arrRawData, arrOutPut
Dim iRow As Long, iCol As Long, iPtr As Long, iptr1 As Long


With Worksheets(cShRawData)
arrRawData = .Range("A1").CurrentRegion
End With
iptr1 = 1
ReDim arrOutPut(1 To UBound(arrRawData) * 5, 1 To 4)
For iRow = 2 To UBound(arrRawData)
For iCol = 7 To 12
If iCol <> 10 Then

iPtr = iPtr + 1

arrOutPut(iPtr, 1) = VBA.Choose(iCol, , , , , , , 0.1, 14.1, 25.1, , 0.1, 27.1)
arrOutPut(iPtr, 2) = VBA.Choose(iCol, , , , , , , 14, 25, 32, , 27, 34)
arrOutPut(iPtr, 3) = arrRawData(iRow, iCol)
arrOutPut(iPtr, 4) = 8000 + iptr1
End If

Next
iptr1 = iptr1 + 1
Next

With Worksheets(cShOutPutData)
.Cells(1, 1).Resize(, 4) = Array("From", "To", "Amount", "Id")
.Cells(2, 1).Resize(UBound(arrOutPut), 4) = arrOutPut
End With
End Sub


output
------
From To Amount Id
0.1 14 551 8001
14.1 25 654 8001
25.1 32 680 8001
0.1 27 727 8001
27.1 34 804 8001
0.1 14 587 8002
14.1 25 690 8002
25.1 32 722 8002
0.1 27 801 8002
27.1 34 885 8002
0.1 14 670 8003
14.1 25 773 8003
25.1 32 812 8003
0.1 27 884 8003
27.1 34 975 8003
0.1 14 551 8004
14.1 25 654 8004


thanks a lot..
regards
MN

sample.xlsx
dear Mike

after I show it to my boss I have got some new ideas from my boss.

On the excel sheet there is a raw data and in this raw data there is a column known as depot e.g., DEHAM, now this is having multiple sub records which is known depot / terminals.
for example as below
DEHAMTEURC
DEHAMTBURC and so on.
for each of these depots, we need a separate excel sheet with the depot name on the sheet.
further in these separate sheets, we need to copy in specific columns into each of the depot sheets which will be created.
this depot sheet should be picking up the header from the sheet named master data, and fill in the respective information from the rawa data sheet to the depot sheet data with the same format as the master data.

once the depot sheet is filled up with the raw data information, as shown in the excel sheet. the remaining fields of master data needs to be autofilled till the end of the records copied from the raw data is there

so ultimately, as shown in the attached , the format will be looking as shown

thanks for your kind help again for working this out. all the information is there in the sample excel sheet attached

regards
MN

dear mike
yes those details logic should be there also, namely the cases, for filling up the summary
from to id
0.1 14 8001
14.1 25 8001, and so on.

thanks again for your reply and consideration

regards
MN

Originally Posted by mike7952

Okay... With the example you uploaded last, can you show exactly how the data will look for "DEHAM" and what columns get pulled from what sheet. I don't see any ID on the master sheet. So if you can show how the sheet for "DEHAM" from the Raw Data sheet will look after the macro is ran will be great.

Dear mike

In the attached there is a sheet named DEHAMTEURC it will look the same way. Ohh sorry i didnt put the ID it should be auto generated like 8000 series.

Raw data shouldd not have any changes.data should be picked up from raw data and should be displayed in the DEHAMTEURC sheet as shown in the attachment

Regards
MN

dear mike
kindly find in the attached the yellow highlighted should be the output sheet and the green is the raw data, for each of these depots fro each cell we need to have different sheet as shown in the yellow highlighted.

steps is as below.

1. the output sheet should first pick up the excel header template form the master data
2. then from the raw data sheet we need to copy the columns from the sheet "depot" as I have put the depots in another sheet for reference. then copy the matching code from this sheet and create an output sheet and then copy the details to the column "C"

Note - for each depot reference, we need to copy all the information from the raw data sheet as mentioned below, and also create new sheet

3. from the raw data sheet we need to copy the columns "D" to column "V"(in output sheet)
4. from the raw data we need to copy the columns "E" to the column "X"(in output sheet)
5. from the raw data we need to copy the columns "N" to the column "Y" (in the output sheet )
6. from the raw data we need to copy the columns "O" to the column "Z" (in the output sheet)

step 1
-------
the header information needs to be copied to the outputdata2 sheet
step 2
------
there is a raw data sheet. From this raw data sheet, the columns to be copied as below:
(from raw data sheet) ==>(outputdata2)
"C" to be copied to column "A" of outputdata2 sheet
"D" to be copied to column "E" of outputdata2 sheet
"E" to be copied to column "G" of outputdat2 sheet
"N" to be copied to column "X" of outputdata2 sheet
"O" to be copied to column "Y" of outputdata2 sheet

step 3
There is a sheet named “depot” where for each look up cell value of the column, it should generate new sheet with the name of the cell value, and the information to be copied from outputdata2.
For example, if there are ‘n’ number of cell value, it should create ‘n’ number of cells, with the details same as in the outputdata2 sheet.
In this sheet, the step 1 and step 2 details to be available.
It should also autofill all the column values from the master data, till the column value is not blank, if we refer suppose the first column of the generated sheet; those shown in blue highlighted.
There is a sample sheet shown with the name “DEHAMTEURC”.
Also at the time of generating a new sheet it should have a unique ID value e,g, starting from 8001, for each sheet generated. This needs to be defined in the “ID” column [AL]

thanks for your urgent help in my request
kindly advise for any further clarifications
I need it urgently..

I am tensed

thanks and regards
MN


sample data.xlsx