Such as Pivot Table but not!

[poyrazozer]

Hi there,

I have a table as attached "Table-1". How could I make it with fastest way to reshape like "Table-2"? My best way to make it is to copy "Module 1" and paste, then filter "Module 1" w/o choosing blanks, copy Part numbers and usage quantities and paste to near of "Module 1". And same processes for all modules. As you can guess, it's taking too many hour because my table is very big.

Waiting for valuable feedbacks.

Table-1
https://ibb.co/c1f0sG

Table-2
https://ibb.co/hd89mb

Thank you.

 

[mrshl9898]

It's hard to be precise without cell references but i'd use a new column with an IF to find the Module Number:

=IF(B2<>"","Module 1",IF(C2<>"","Module 2",IF(D2<>"","Module 3","")))

Then a sum to get the usage

=SUM(B2:D2)

 

[poyrazozer]

You can find my revised table with column and row numbers in here.

Could you please tell me to write exact formula to which cell?

Thank you.

https://ibb.co/fj6WXG

 

[mrshl9898]

Just had another look.... I think VBA is the best solution.

Below works on your example (just delete the second table, this creates it). Note this only works if the sheet is already selected:

Sub createtable()


Dim rownum As Long
Dim colnum As Long
Dim rownum2 As Long
Dim counter As Long


Range("F2").Value = "Module No"
Range("G2").Value = "Part No"
Range("H2").Value = "Usage"


rownum = 3
colnum = 2
rownum2 = 3
counter = 0


Do Until counter = 3
Do Until Cells(rownum, 1).Value = ""
If Cells(rownum, colnum).Value <> "" Then
Cells(rownum2, 6).Value = Cells(2, colnum).Value
Cells(rownum2, 7).Value = Cells(rownum, 1).Value
Cells(rownum2, 8).Value = Cells(rownum, colnum).Value
rownum2 = rownum2 + 1
End If
rownum = rownum + 1
Loop
rownum = 3
colnum = colnum + 1
counter = counter + 1
Loop


End Sub

 

[poyrazozer]

Hi mrshl9898,

Sorry I'm not familiar to VBA code unfortunately. If I want to implement this code (it worked for my sample table by the way) how could I fix or edit this VBA code? I couldn't understand which row and values are doing what.

Thanks for support.

 

[mrshl9898]

The best way to work through it is when in the VBA editor press F8 to go through the code line by line.

Then you can stop on a line you are unsure of and hover the mouse over the variables (rownum etc) to see their current value.

 

[Joe4]

Cross-posted: https://www.excelforum.com/excel-formulas-and-functions/1213412-such-as-pivot-table-but-not.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html).

This way, other members can see what has already been done in regard to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: http://www.excelguru.ca/content.php?184).