Dynamic Table

ManUBlueJay

Active Member
Joined
Aug 30, 2012
Messages
257
Office Version
  1. 2016
Platform
  1. Windows
I have a very large Date Table 8 Cols wide 10,000 rows long. (Called Table1)
I am trying to make a new table 20 rows long (Called Table2) that will index a certain part of this table, it will be dynamic based on a Chosen name.
This will allow me to work on a small table.
I am able to extract the range from the large table and have named it TestA
I would like Table2 starting with the first non header row represent what is in TestA range.
Table2, row 1 would be the first line of TestA range.
Table2 row 2 would be the 2nd line of TestA range.............

Thanks in advance
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows
What sort of solution are you looking for, formula based or vba? Which version of excel (please update your profile to show this rather than just posting it here so that it is there for people to see with all of your questions not just this one).

In office 365 it is as simple as typing =TestA into the top left cell of Table2 then the dynamic array functionality will do the rest for you, in other versions it will take a bit more effort.
That said curiosity has me wondering why you don't simply filter the original table :unsure:
 

ManUBlueJay

Active Member
Joined
Aug 30, 2012
Messages
257
Office Version
  1. 2016
Platform
  1. Windows
Thanks for the heads-up. Changed profile but for those viewing here it is Windows 16, 64bit.
I would prefer a formula based solution in Table2.
I am running a log complicated controlled update, and it is quite time consuming to go back and forth. I will do further filtration in Table 2. If I just filter Table1 the new filtration Data would need to be on the whole table.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows
Now I follow.

See if this works. Enter this into the top left cell of Table2, changing all references to A2 to the same cell that the formula is into, making sure that the three $ signs are positioned the same. Once entered, used the fill handle to drag right and down as needed.

Excel Formula:
=IF(ROWS(A$2:A2)>ROWS(TestA),"",INDEX(TestA,ROWS(A$2:A2),COLUMNS($A2:A2)))
 

ManUBlueJay

Active Member
Joined
Aug 30, 2012
Messages
257
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thank you. If I use your formula straight up it brings back an error. Digging further index is not recognizing my TestA range, it seems to brings back my formula as text suronding it with Quatation marks. When I view it Formulas Name Manager it selects my range. This is my Formula to find my Range: OFFSET(Data!$C$1,MATCH(CurrentFamilySortOrder,A_DataFamilySortOrderRange,0),0,COUNTIF(A_DataFamilySortOrderRange,CurrentFamilySortOrder)-1)
You could also possible build it into your index. The Unique number for this family is CurrentFamilySortOrder.
I just don't quite understand how index works to be able to continue by myself
 

ManUBlueJay

Active Member
Joined
Aug 30, 2012
Messages
257
Office Version
  1. 2016
Platform
  1. Windows
I have solved it by putting my TestA formula right into your index formula.
Thanks a lot
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Seeing the range as text in the named range is normal, I'm not sure why it has worked with the definition in the formula but not in the name manager.

It's not how I would have done it, but if it works and you're happy with it then all is well.
 

ManUBlueJay

Active Member
Joined
Aug 30, 2012
Messages
257
Office Version
  1. 2016
Platform
  1. Windows
How would you find a unique range in a long list. I have used the offset/match using Height and Width many times because I understand how it works and use very simple functions. But I am always open to finding better ways. Ultimitaly I want to find the sortcode in my data range. The names I am after are 3 Cols to the left of that sort code, and however many sortcodes (exact match) long
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows
Without having the data to evaluate, this is best guess based on what I can see from your current formulas. I'm going to use row 1 for a few extra formulas and start table2 in row 3 (leaving row 2 for headers if required).

In A1,
Excel Formula:
=MATCH(CurrentFamilySortOrder,A_DataFamilySortOrderRange,0)
In B1,
Excel Formula:
=COUNTIF(A_DataFamilySortOrderRange,CurrentFamilySortOrder)

In A3, then filled right and down as needed.
Excel Formula:
=IF(ROWS(A$3:A3)>$B$1,"",INDEX(Data!C:C,$A$1+ROWS(A$3:A3)-1))

When I looked at your formulas it appeared that you were skipping the first row of the matching part of the table, if that is correct then the -1 at the end of the third formula will need to be removed and put at the end of the countif formula instead.

By having COUNTIF and MATCH each in one cell instead of both in every cell there is a lot less calculation effort required, also by removing the OFFSET function the formula is now non- volatile so will result in less frequent recalculation.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,037
Messages
5,639,678
Members
417,104
Latest member
Nelsini

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