Excel Experts? Rearrange data

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
Post Reply
Topic Author
rich126
Posts: 4475
Joined: Thu Mar 01, 2018 3:56 pm

Excel Experts? Rearrange data

Post by rich126 »

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 ?
----------------------------- | 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.
solarcub
Posts: 253
Joined: Sat Feb 08, 2020 9:09 pm

Re: Excel Experts? Rearrange data

Post by solarcub »

Looks like a pivot table might be the thing, if I understand your question.
FactualFran
Posts: 2777
Joined: Sat Feb 21, 2015 1:29 pm

Re: Excel Experts? Rearrange data

Post by FactualFran »

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.

Code: Select all

Company   price   volume   high
Apple
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

Code: Select all

Company   Attribute   Value
Apple     price
Apple     volume
Apple     high
then you can create a pivot table with Company as the row field, Attribute as the column field, and Value as the Data Field.
Count of Notre Dame
Posts: 448
Joined: Fri Oct 11, 2013 1:08 pm

Re: Excel Experts? Rearrange data

Post by Count of Notre Dame »

There's a new xlookup function that makes all this really easy.
User avatar
BruceM
Posts: 1851
Joined: Fri Aug 08, 2008 1:09 pm
Location: Manzanita, Oregon

Re: Excel Experts? Rearrange data

Post by BruceM »

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
calabel
Posts: 49
Joined: Fri Sep 15, 2017 4:14 pm

Re: Excel Experts? Rearrange data

Post by calabel »

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.
runninginvestor
Posts: 1796
Joined: Tue Sep 08, 2020 8:00 pm

Re: Excel Experts? Rearrange data

Post by runninginvestor »

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 ?
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, ;

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
Then use a lookup or Index&Match off the hlper column.
A2_MI
Posts: 20
Joined: Mon Oct 05, 2020 3:26 pm

Re: Excel Experts? Rearrange data

Post by A2_MI »

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.
JBTX
Posts: 11228
Joined: Wed Jul 26, 2017 12:46 pm

Re: Excel Experts? Rearrange data

Post by JBTX »

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.
User avatar
Oicuryy
Posts: 1959
Joined: Thu Feb 22, 2007 9:29 pm

Re: Excel Experts? Rearrange data

Post by Oicuryy »

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.

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
Copy this code box into cell A1 of Sheet2 of the same workbook.

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)
Ron
Money is fungible | Abbreviations and Acronyms
nano
Posts: 35
Joined: Sat Dec 05, 2020 12:04 pm

Re: Excel Experts? Rearrange data

Post by nano »

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).
GrowthSeeker
Posts: 1071
Joined: Tue May 15, 2018 10:14 pm

Re: Excel Experts? Rearrange data

Post by GrowthSeeker »

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.
Topic Author
rich126
Posts: 4475
Joined: Thu Mar 01, 2018 3:56 pm

Re: Excel Experts? Rearrange data

Post by rich126 »

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.
----------------------------- | 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.
GrowthSeeker
Posts: 1071
Joined: Tue May 15, 2018 10:14 pm

Re: Excel Experts? Rearrange data

Post by GrowthSeeker »

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.
Just because you're paranoid doesn't mean they're NOT out to get you.
basspond
Posts: 1500
Joined: Wed Nov 27, 2013 3:01 am

Re: Excel Experts? Rearrange data

Post by basspond »

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)
inverter
Posts: 1021
Joined: Mon Jul 27, 2015 1:40 pm
Location: New York, NY

Re: Excel Experts? Rearrange data

Post by inverter »

You can copy the data and paste it using Transpose under “Paste Special”.
Post Reply