Excel Experts? Rearrange data
Excel Experts? Rearrange data
I'm fairly proficient at doing a lot of data manipulation in excel but I'm trying to figure out if there is a somewhat easy way to rearrange data. Probably easier to show an example rather than explain it.
Current Data:
Apple price
Apple volume
Apple high
Microsoft price
Microsoft volume
Preferred format:
Price Volume High
Apple
Microsoft
So the current data has multiple rows with the same company (in my example) or you can think of it as a unique key. The problem is that not every "key" will have the same number of rows and I don't know how to parse the data to get it into the format of the latter.
I know I can use something like vlookup based on the key and grab the data by the column number but then how do I grab the 2nd value since vlookup matches the first entry. It isn't as if you can tell vlookup to "continue to next occurrence". vlookup seems best suited to datasets where you have only the unique key occurring once in a column, not multiple times.
Am I overlooking an Excel function ?
Current Data:
Apple price
Apple volume
Apple high
Microsoft price
Microsoft volume
Preferred format:
Price Volume High
Apple
Microsoft
So the current data has multiple rows with the same company (in my example) or you can think of it as a unique key. The problem is that not every "key" will have the same number of rows and I don't know how to parse the data to get it into the format of the latter.
I know I can use something like vlookup based on the key and grab the data by the column number but then how do I grab the 2nd value since vlookup matches the first entry. It isn't as if you can tell vlookup to "continue to next occurrence". vlookup seems best suited to datasets where you have only the unique key occurring once in a column, not multiple times.
Am I overlooking an Excel function ?
----------------------------- |
If you think something is important and it doesn't involve the health of someone, think again. Life goes too fast, enjoy it and be nice.
Re: Excel Experts? Rearrange data
Looks like a pivot table might be the thing, if I understand your question.
-
- Posts: 2777
- Joined: Sat Feb 21, 2015 1:29 pm
Re: Excel Experts? Rearrange data
Because I don't use Excel, I can't provide an Excel specific approach. With spreadsheet software in general, you could do a vlookup on each of the wanted values in the source column. For example, in the result row for Apple do a vlookup on "Apple price" in the first column, on "Apple volume" in the second, "Apple High" in the third, and so on.
You can arrange the result cell range to be like the following.
The formula in the price column would be vlookup($a2&" "&b$1,CurrentDataRange,2,0), where $a1 is the cell of the result range containing "Company". The formula in the volume and high columns would be copies of the formula in the price column.
If you can massage the Current Data so the Company Name and Attribute are in separate columns, as in
then you can create a pivot table with Company as the row field, Attribute as the column field, and Value as the Data Field.
You can arrange the result cell range to be like the following.
Code: Select all
Company price volume high
Apple
If you can massage the Current Data so the Company Name and Attribute are in separate columns, as in
Code: Select all
Company Attribute Value
Apple price
Apple volume
Apple high
-
- Posts: 448
- Joined: Fri Oct 11, 2013 1:08 pm
Re: Excel Experts? Rearrange data
There's a new xlookup function that makes all this really easy.
Re: Excel Experts? Rearrange data
Rich126
The columns to the right of each data title would be headed by a date, correct? (day, week, month or year)
Are you asking how to sort to re-order the data title? For example, to put all the stocks price in one group and the stocks volume in another group and so on? If so, the only way I can think to do that is create another column to the left of the data titles and then go down and put numbers next to the rows you wish to combine, then do a sort on the number column for the entire sheet. A bit clunky and I'd bet there's some kind of Excel function that will do this, but I can't think of what it would be
BruceM
The columns to the right of each data title would be headed by a date, correct? (day, week, month or year)
Are you asking how to sort to re-order the data title? For example, to put all the stocks price in one group and the stocks volume in another group and so on? If so, the only way I can think to do that is create another column to the left of the data titles and then go down and put numbers next to the rows you wish to combine, then do a sort on the number column for the entire sheet. A bit clunky and I'd bet there's some kind of Excel function that will do this, but I can't think of what it would be
BruceM
Re: Excel Experts? Rearrange data
You also might want to look into index match as a solution to get around the vlookup "first column" limitation. I haven't used xlookup yet since I'm pretty comfortable with index match and index match match, but I suspect it may be a less intimidating way to get to a similar point.
-
- Posts: 1796
- Joined: Tue Sep 08, 2020 8:00 pm
Re: Excel Experts? Rearrange data
Not sure if you'll always know that the first obs for each security is the price, seconds, volume, etc If so, just make a helper key by indexing within each security. Ie, ;rich126 wrote: ↑Wed Mar 10, 2021 2:37 pm ...
So the current data has multiple rows with the same company (in my example) or you can think of it as a unique key. The problem is that not every "key" will have the same number of rows and I don't know how to parse the data to get it into the format of the latter.
I know I can use something like vlookup based on the key and grab the data by the column number but then how do I grab the 2nd value since vlookup matches the first entry. It isn't as if you can tell vlookup to "continue to next occurrence". vlookup seems best suited to datasets where you have only the unique key occurring once in a column, not multiple times.
Am I overlooking an Excel function ?
Code: Select all
Helper Index Security
AAPL1 1 AAPL
AAPL2 2 AAPL
AAPL3 3 AAPL
MSFT1 1 MSFT
MSFT2 2 MSFT
MSFT3 3 MSFT
MSFT4 4 MSFT
Re: Excel Experts? Rearrange data
OP can you post a screen shot of some of the original data to clarify your problem? Seeing it “in black and white” would help get closer to whether this needs advanced formulas or if the Xlookup or pivot table would be a simple solution.
Re: Excel Experts? Rearrange data
I found in some instances using Index and match functions was more powerful, although slightly more complicated. Google “index match excel” and you can find tutorials.
If you are just trying to take existing raw data, and moving from rows to columns or vice versa, a few Copy>>paste special >>transpose may be the easiest way to go.
If you are just trying to take existing raw data, and moving from rows to columns or vice versa, a few Copy>>paste special >>transpose may be the easiest way to go.
Re: Excel Experts? Rearrange data
This combines runninginvestor's helper column with FactualFran's vlookup. Copy and paste this code box into cell A1 of Sheet1 of a new workbook. Do text to columns using # as the separator.
Copy this code box into cell A1 of Sheet2 of the same workbook.
Ron
Code: Select all
=B1&C1#Apple#price#100
=B2&C2#Apple#volume#200
=B3&C3#Apple#high#300
=B4&C4#Microsoft#price#400
=B5&C5#Microsoft#volume#500
Code: Select all
#price#volume#high
Apple#=VLOOKUP($A2&B$1,Sheet1!$A$1:$D$5,4,FALSE)#=VLOOKUP($A2&C$1,Sheet1!$A$1:$D$5,4,FALSE)#=VLOOKUP($A2&D$1,Sheet1!$A$1:$D$5,4,FALSE)
Microsoft#=VLOOKUP($A3&B$1,Sheet1!$A$1:$D$5,4,FALSE)#=VLOOKUP($A3&C$1,Sheet1!$A$1:$D$5,4,FALSE)#=VLOOKUP($A3&D$1,Sheet1!$A$1:$D$5,4,FALSE)
Money is fungible |
Abbreviations and Acronyms
Re: Excel Experts? Rearrange data
It's not clear your structure, but I am assuming your columns are something like:
Date(A), Symbol(B), Attribute(C), Value(D)
1/1, AAPL, Price, 100.00
Assuming so:
* Pivot table
* Use the UNIQUE function to generate unique rows (ie. =UNIQUE(A:B)) then use a preferred function to get the value (ie. vlookup, sumifs, etc)
Note: You can use vlookups with multiple fields. You just need to do it as a composite key, such as =A&B (both the lookup value and the range values).
Date(A), Symbol(B), Attribute(C), Value(D)
1/1, AAPL, Price, 100.00
Assuming so:
* Pivot table
* Use the UNIQUE function to generate unique rows (ie. =UNIQUE(A:B)) then use a preferred function to get the value (ie. vlookup, sumifs, etc)
Note: You can use vlookups with multiple fields. You just need to do it as a composite key, such as =A&B (both the lookup value and the range values).
-
- Posts: 1071
- Joined: Tue May 15, 2018 10:14 pm
Re: Excel Experts? Rearrange data
Not sure how many stocks you’re following, or your data source; but thinking outside the box, what about just collecting data from scratch such as from finance.yahoo.com
Just because you're paranoid doesn't mean they're NOT out to get you.
Re: Excel Experts? Rearrange data
I was only using stocks as an example. It is actually sports related but since this is a financial forum I modified the data
I'll have to check into the alternate key thing.
I'll ponder things and will update as needed (i.e., if I find a good solution or get to a point where I may get stuck). I'm currently manipulating a bunch of other data right now.
I'll have to check into the alternate key thing.
I'll ponder things and will update as needed (i.e., if I find a good solution or get to a point where I may get stuck). I'm currently manipulating a bunch of other data right now.
----------------------------- |
If you think something is important and it doesn't involve the health of someone, think again. Life goes too fast, enjoy it and be nice.
-
- Posts: 1071
- Joined: Tue May 15, 2018 10:14 pm
Re: Excel Experts? Rearrange data
The more messy the source data is, the more difficult it is to clean up using a spreadsheet. Might be easier with a computer program.
If we saw a sample of actual data it would be easier to advise. It’s OK AFAIK that it’s sports rather than stocks. It’s a preprocessing the input data science problem.
If we saw a sample of actual data it would be easier to advise. It’s OK AFAIK that it’s sports rather than stocks. It’s a preprocessing the input data science problem.
Just because you're paranoid doesn't mean they're NOT out to get you.
Re: Excel Experts? Rearrange data
Don’t know exactly how your data is formatted but many times I would have to analyze data that was in a report format and missing vital column information. I used a combination of data/text to column reformatting then would have to add a column with the information using IF statements for new column C =If(B3=“”;B2,B3)
Re: Excel Experts? Rearrange data
You can copy the data and paste it using Transpose under “Paste Special”.