Arranging the database by columns

O

Omer_K

Board Regular
Joined
Apr 9, 2017
Messages
124
Office Version
  1. 365
Hello everyone,
I have a database with dates, customer (for the the example I called them customer 1, customer 2, etc.), values and countries
I want to create the database in the range R2: T14
Depending on the country we selected in the data validation list in cell V2

Hope this is clear :)

Data.xlsx
ABCDEFGHIJKLMNOPRSTUV
1Dateclient 1Countryclient 2Countryclient 3Countryclient 4Countryclient 5Countryclient 6CountryDateValueClient No.Choose Cuntry
206/10/202178Italy4Germany11Netherlands11Netherlands99Italy17Argentina06/10/202178client 1Italy
307/10/202178Italy4Germany11Italy11Netherlands99Italy17Argentina06/10/202199client 5
413/10/202178Italy4Germany11Netherlands5Germany99Italy17Argentina07/10/202178client 1
514/10/202178Italy4Germany11Peru3Germany99Italy17Argentina07/10/202111client 3
620/10/202180Italy4Argentina11Italy3Germany99Italy17Argentina07/10/202199client 5
721/10/202180Germany4Argentina12Germany3Italy99Netherlands17New Zealand13/10/202178client 1
827/10/202180Germany4Argentina12Germany3Argentina99Netherlands17New Zealand13/10/202199client 5
928/10/202178Germany4Argentina12Germany3Argentina54Netherlands17New Zealand14/10/202178client 1
1003/11/202178Germany4Argentina12Peru3Argentina54Netherlands17New Zealand14/10/202199client 5
1104/11/202178Germany4Argentina12Peru3Argentina54Netherlands17New Zealand20/10/202180client 1
1220/10/202111client 3
1320/10/202199client 5
1421/10/20213client 4
15
16
17
18
Data
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:M11Cell Value=$V$2textNO
Cells with Data Validation
CellAllowCriteria
V2List=$Q$10:$Q$15
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
I couldn't figure out how to do it without a helper table.
MrExcelPlayground4.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Dateclient 1Countryclient 2Countryclient 3Countryclient 4Countryclient 5Countryclient 6CountryDateValueClient No.Choose CuntryHelper Data
210/6/202178Italy4Germany11Netherlands11Netherlands99Italy17Argentina10/6/202178client 1Italy10/6/202178client 1Italy
310/7/202178Italy4Germany11Italy11Netherlands99Italy17Argentina10/6/202199client 510/6/20214client 2Germany
410/13/202178Italy4Germany11Netherlands5Germany99Italy17Argentina10/7/202178client 110/6/202111client 3Netherlands
510/14/202178Italy4Germany11Peru3Germany99Italy17Argentina10/7/202111client 310/6/202111client 4Netherlands
610/20/202180Italy4Argentina11Italy3Germany99Italy17Argentina10/7/202199client 510/6/202199client 5Italy
710/21/202180Germany4Argentina12Germany3Italy99Netherlands17New Zealand10/13/202178client 110/6/202117client 6Argentina
810/27/202180Germany4Argentina12Germany3Argentina99Netherlands17New Zealand10/13/202199client 510/7/202178client 1Italy
910/28/202178Germany4Argentina12Germany3Argentina54Netherlands17New Zealand10/14/202178client 110/7/20214client 2Germany
1011/3/202178Germany4Argentina12Peru3Argentina54Netherlands17New Zealand10/14/202199client 510/7/202111client 3Italy
1111/4/202178Germany4Argentina12Peru3Argentina54Netherlands17New Zealand10/20/202180client 110/7/202111client 4Netherlands
1210/20/202111client 310/7/202199client 5Italy
1310/20/202199client 510/7/202117client 6Argentina
1410/21/20213client 410/13/202178client 1Italy
1510/13/20214client 2Germany
1610/13/202111client 3Netherlands
1710/13/20215client 4Germany
1810/13/202199client 5Italy
1910/13/202117client 6Argentina
2010/14/202178client 1Italy
2110/14/20214client 2Germany
2210/14/202111client 3Peru
2310/14/20213client 4Germany
2410/14/202199client 5Italy
2510/14/202117client 6Argentina
2610/20/202180client 1Italy
2710/20/20214client 2Argentina
2810/20/202111client 3Italy
2910/20/20213client 4Germany
3010/20/202199client 5Italy
3110/20/202117client 6Argentina
3210/21/202180client 1Germany
3310/21/20214client 2Argentina
3410/21/202112client 3Germany
3510/21/20213client 4Italy
3610/21/202199client 5Netherlands
3710/21/202117client 6New Zealand
3810/27/202180client 1Germany
3910/27/20214client 2Argentina
4010/27/202112client 3Germany
4110/27/20213client 4Argentina
4210/27/202199client 5Netherlands
4310/27/202117client 6New Zealand
4410/28/202178client 1Germany
4510/28/20214client 2Argentina
4610/28/202112client 3Germany
4710/28/20213client 4Argentina
4810/28/202154client 5Netherlands
4910/28/202117client 6New Zealand
5011/3/202178client 1Germany
5111/3/20214client 2Argentina
5211/3/202112client 3Peru
5311/3/20213client 4Argentina
5411/3/202154client 5Netherlands
5511/3/202117client 6New Zealand
5611/4/202178client 1Germany
5711/4/20214client 2Argentina
5811/4/202112client 3Peru
5911/4/20213client 4Argentina
6011/4/202154client 5Netherlands
6111/4/202117client 6New Zealand
Sheet9
Cell Formulas
RangeFormula
R2:T14R2=FILTER(X2:Z61,AA2#=V2)
X2:X61X2=INDEX(A2:A11,INT((SEQUENCE(ROWS(B2:M11)*COLUMNS(B2:M11)/2)-1)/(COLUMNS(B2:M11)/2))+1)
Y2:Y61Y2=INDEX(B2:M11,INT((SEQUENCE(ROWS(B2:M11)*COLUMNS(B2:M11)/2)-1)/(COLUMNS(B2:M11)/2))+1,(MOD(SEQUENCE(ROWS(B2:M11)*COLUMNS(B2:M11)/2)-1,COLUMNS(B2:M11)/2)+1)*2-1)
Z2:Z61Z2=INDEX(B1:M1,(MOD(SEQUENCE(ROWS(B2:M11)*COLUMNS(B2:M11)/2)-1,COLUMNS(B2:M11)/2)+1)*2-1)
AA2:AA61AA2=INDEX(B2:M11,INT((SEQUENCE(ROWS(B2:M11)*COLUMNS(B2:M11)/2)-1)/(COLUMNS(B2:M11)/2))+1,(MOD(SEQUENCE(ROWS(B2:M11)*COLUMNS(B2:M11)/2)-1,COLUMNS(B2:M11)/2)+1)*2)
Dynamic array formulas.
 
Upvote 0
Another option without helpers.
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTUV
1Dateclient 1Countryclient 2Countryclient 3Countryclient 4Countryclient 5Countryclient 6CountryDateValueClient No.Choose Country
206/10/202178Italy4Germany11Netherlands11Netherlands99Italy17Argentina06/10/202178client 1Italy
307/10/202178Italy4Germany11Italy11Netherlands99Italy17Argentina06/10/202199client 5
413/10/202178Italy4Germany11Netherlands5Germany99Italy17Argentina07/10/202178client 1
514/10/202178Italy4Germany11Peru3Germany99Italy17Argentina07/10/202111client 3
620/10/202180Italy4Argentina11Italy3Germany99Italy17Argentina07/10/202199client 5
721/10/202180Germany4Argentina12Germany3Italy99Netherlands17New Zealand13/10/202178client 1
827/10/202180Germany4Argentina12Germany3Argentina99Netherlands17New Zealand13/10/202199client 5
928/10/202178Germany4Argentina12Germany3Argentina54Netherlands17New Zealand14/10/202178client 1
1003/11/202178Germany4Argentina12Peru3Argentina54Netherlands17New Zealand14/10/202199client 5
1104/11/202178Germany4Argentina12Peru3Argentina54Netherlands17New Zealand20/10/202180client 1
1220/10/202111client 3
1320/10/202199client 5
1421/10/20213client 4
15
16
Lists
Cell Formulas
RangeFormula
R2:T14R2=LET(rngD,A2:A11,rngC,B2:M11,Rws,ROWS(rngD),Qty,SEQUENCE(Rws*COLUMNS(rngC)/2,,0),Ary,CHOOSE({1,2,3},INDEX(rngD,MOD(Qty,Rws)+1),INDEX(rngC,MOD(Qty,Rws)+1,INT(Qty/Rws)*2+1),INDEX(B1:M1,INT(Qty/Rws)*2+1)),SORT(FILTER(Ary,INDEX(rngC,MOD(Qty,Rws)+1,INT(Qty/Rws)*2+2)=V2)))
Dynamic array formulas.
 
Upvote 0
Yup, it's definitely very useful at times
 
Upvote 0
A slight shorter formula
Excel Formula: 
=LET(rngD,A2:A11,rngC,B2:M11,Rws,ROWS(rngD),Qty,SEQUENCE(Rws*COLUMNS(rngC)/2,,0),AryC,INDEX(rngC,MOD(Qty,Rws)+1,INT(Qty/Rws)*2+{1,2}),Ary,CHOOSE({1,2,3},INDEX(rngD,MOD(Qty,Rws)+1),INDEX(AryC,,1),INDEX(B1:M1,INT(Qty/Rws)*2+1)),SORT(FILTER(Ary,INDEX(AryC,,2)=V2)))
 
Upvote 0
Not sure which formula you are referring to, but glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

MrDB4Excel
  • Question
If Find Blank Cell
Replies
0
Views
398
MrDB4Excel
MrDB4Excel
R
  • Question
Rearranging Columns in Pivot Table
Replies
8
Views
468
SpillerBD
S
MrDB4Excel
  • Solved
If Exist in a Column Range
Replies
8
Views
936
DanteAmor
DanteAmor
K
  • Solved
formula for the average of cells across columns with multiple conditions
Replies
4
Views
435
kckliodna
K
E
  • Solved
Auto populating cells based on a different cell
Replies
9
Views
517
smozgur
smozgur

Forum statistics

Threads
1,215,056
Messages
6,122,907
Members
449,096
Latest member
dbomb1414

Share this page

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