VBA Reorder/Transpose data

anthonyexcel

Active Member
Joined
Jun 10, 2011
Messages
258
Office Version
  1. 365
Platform
  1. Windows
Good morning,

I need some help, I need VBA to re-org/transpose the data below for our lottery system. Please see the breakdown the results I need start in Column I. Thanks in advance!

Book1
ABCDEFGHIJKLMNO
1IDFnameLnameRankSCHOOLIDFnameLname1234
258MJones1LINC58MJonesLINCHOOVKEN
358MJones2HOOV334SSampsonHOOV
458MJones3KEN378ASmithHOOV
5334SSampson1HOOV422DRandolphHOOVKEN
6378ASmith1HOOV995TReyesLINCROOS
7422DRandolph1HOOV1172APaulLINC
8422DRandolph2KEN1513BSmithKEN
9995TReyes1LINC1541CSmithKEN
10995TReyes2ROOS1798WJacksonHOOVWASHLINCKEN
111172APaul1LINC1897TJohnsonLINCWASH
121513BSmith1KEN2493ARodriguezKENLINC
131541CSmith1KEN2563BBellHOOVWASHLINCROOS
141798WJackson1HOOV2664JKimKEN
151798WJackson2WASH
161798WJackson3LINC
171798WJackson4KEN
181897TJohnson1LINC
191897TJohnson2WASH
202493ARodriguez1KEN
212493ARodriguez2LINC
222563BBell1HOOV
232563BBell2WASH
242563BBell3LINC
252563BBell4ROOS
262664JKim1KEN
Sheet1
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Book1
IJKLMNO
1IDFnameLname1234
258MJonesLINCHOOVKEN
3334SSampsonHOOV
4378ASmithHOOV
5422DRandolphHOOVKEN
6995TReyesLINCROOS
71172APaulLINC
81513BSmithKEN
91541CSmithKEN
101798WJacksonHOOVWASHLINCKEN
111897TJohnsonLINCWASH
122493ARodriguezKENLINC
132563BBellHOOVWASHLINCROOS
142664JKimKEN
Sheet1
Cell Formulas
RangeFormula
I2:I14I2=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"")))
J2:K14J2=IF($I2<>"",VLOOKUP($I2,$A:$C,COLUMN()-8,0),"")
L2:N2,L13:O13,L11:M12,L10:O10,L5:M6,L3:L4,L7:L9,L14L2=IF($I2<>"",TRANSPOSE(FILTER($E:$E,$A:$A=$I2)),"")
Dynamic array formulas.
 
Upvote 0
With Power Pivot, create a DAX measure and place that in the value field.

Book3.xlsx
IJKLMNO
17SchoolsRank
18IDFnameLname1234
1958MJonesLINCHOOVKEN
20334SSampsonHOOV
21378ASmithHOOV
22422DRandolphHOOVKEN
23995TReyesLINCROOS
241172APaulLINC
251513BSmithKEN
261541CSmithKEN
271798WJacksonHOOVWASHLINCKEN
281897TJohnsonLINCWASH
292493ARodriguezKENLINC
302563BBellHOOVWASHLINCROOS
312664JKimKEN
Sheet1
 

Attachments

  • capture4.jpg
    capture4.jpg
    51.1 KB · Views: 4
  • capture3.jpg
    capture3.jpg
    32.8 KB · Views: 4
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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
Back
Top