Such as Pivot Table but not!

poyrazozer

New Member
Joined
Dec 26, 2017
Messages
3
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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,623
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

New Member
Joined
Dec 26, 2017
Messages
3
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.

fj6WXG
https://ibb.co/fj6WXG
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,623
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:

Code:
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
 
Last edited:

poyrazozer

New Member
Joined
Dec 26, 2017
Messages
3

ADVERTISEMENT

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

Well-known Member
Joined
Feb 6, 2012
Messages
1,623
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,539
Messages
5,596,754
Members
414,097
Latest member
FaeFen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top