Symbol list in Spreadsheet

[Data123]

I have a spreadsheet with three sheet tabs (+) at the bottom. The spreadsheet as well as each sheet tab has the same symbol/name list (about 500 stock symbols) in them. To make things easier to follow let's call the spreadsheet the dashboard. The dashboard simply has columns titled; name, symbol, price, volume and several price change %'s. So some of the dashboard's formulas connects to the other sheets data ie., price change %. So I noticed on the dashboard when I sort one of the "price change %" columns, I also choose to expand the sort to the other columns in the dashboard and the symbols shuffle as they should. However, the symbol list in the other sheets stay in their original order.

So my question for you is how can I put the dashboard symbol list back into it's original order to match the order of the symbol list of the other sheets (the original order is not alphabetical)? After reading this myself I realize how difficult it is to imagine. Please see below.

Please keep in mind the number of symbols I have (500+)

Spreadsheet/Dashboard after sorting and expanding a price change % column the other columns as well as the name/symbol list becomes mixed up.

Name
Ishares abcd
Quandrus mrfd
Charles tosd
Blackrock aksj

3 sheets (+) at the bottom of the Spreadsheet/dashboard has the original name/symbol list after I sorted the main spreadsheet/dashboard.

Name
Quandrus mrfd
Ishares abcd
Blackrock aksj
Charles tosd

*****Today I pressed ctrl-z many times to get the spreadsheet/dashboard symbol list back in order, but then had to redo many changes that were reversed. Thanks for your input!

 

[Peter_SSs]

I have done this on a single sheet but see if this concept could work for you.

My actual data is in columns B:C. It was in A:B but I have inserted a new column A and numbered the rows sequentially.
I've then repeated the table in F:G
In my test I then

• Hid columns A and F
• Sorted the second table (with expand the selection) by column H
• I then re-sorted the second table (with expand the selection) by column F ascending (even though it is hidden)

The table was then back in the original order

24 03 09.xlsm
	A	B	C	D	E	F	G	H
1
2	1	a	4			1	a	4
3	2	e	3			2	e	3
4	3	d	5			3	d	5
5	4	s	5			4	s	5
6	5	w	6			5	w	6
7	6	d	4			6	d	4
8	7	c	12			7	c	12
9	8	f	4			8	f	4
10	9	g	7			9	g	7
Re-sort

 

[Data123]

I have done this on a single sheet but see if this concept could work for you.

My actual data is in columns B:C. It was in A:B but I have inserted a new column A and numbered the rows sequentially.
I've then repeated the table in F:G
In my test I then

• Hid columns A and F
• Sorted the second table (with expand the selection) by column H
• I then re-sorted the second table (with expand the selection) by column F ascending (even though it is hidden)

The table was then back in the original order

24 03 09.xlsm
	A	B	C	D	E	F	G	H
1
2	1	a	4			1	a	4
3	2	e	3			2	e	3
4	3	d	5			3	d	5
5	4	s	5			4	s	5
6	5	w	6			5	w	6
7	6	d	4			6	d	4
8	7	c	12			7	c	12
9	8	f	4			8	f	4
10	9	g	7			9	g	7
Re-sort

Thanks Peter, that is a clever solution. Just so I am clear when you hid the columns then sorted the visible column with expand did it shuffle the hidden columns also? If so then you made the hidden columns visible and resorted column F back to its original state based on column F values being 1-9?

If this is true it may not work for me because the symbol list I have is not in alphabetical order (it's mixed up). I am attaching a link to a mock Google Drive Excel spreadsheet for you to see here Loading Google Sheets .So for the workbook the ETFs sheet and the Data sheet have the same Name (symbol list order). If you sort any columns in the ETFs sheet with expand, it will mix up the Name order, but now it will not match the Name order in the Data sheet. I don't mind this, but at some point I need both Name columns to match each other.

 

[Data123]

I just thought I could add a numbered column, would this be a good idea?

 

[Peter_SSs]

Just so I am clear when you hid the columns then sorted the visible column with expand did it shuffle the hidden columns also?

Yes. Did you try it with a sample like mine?

If so then you made the hidden columns visible

Once I hid the columns I didn't ever again make them visible. Just each time I did a sort I chose "expand"

If this is true it may not work for me because the symbol list I have is not in alphabetical order (it's mixed up)

I'm not sure why not as my list in column B (it was column A until I inserted a new column as described) is also not in alphabetical order.

I just thought I could add a numbered column, would this be a good idea?

That is exactly what my suggestion is.


I have not yet taken a look at your linked file but will try to in a while.

 

[Peter_SSs]

I have not yet taken a look at your linked file but will try to in a while.

OK, I have had a look at the sample sheet and my suggestion seems to work fine as I understand the requirement. To recap, this is what I did.

1. On 'ETFs' I inserted a new column A and
   entered the numbers 1-743 in column A starting in row 2 (by entering a 1 in A2, 2 in A3 then selecting A2:A3 and double-clicking the Fill Handle) and
   hid column A
2. ** On 'Data', I repeated the above steps.
3. On 'ETFs' I sorted the data by Volume and then sorted again choosing (Column A) in the 'Sort by' box of the Sort dialog

** If you are not going to be sorting/resorting the 'Data' sheet then there is no need to introduce that new column A on the 'Data' sheet.

 

[Data123]

Thank you!

 

[Peter_SSs]

You are welcome.