Excel 2010 Move data from multiple columns/rows to single row
I have several thousand rows of data. Each has 4 columns associated with it that may contain a number. I need to identify duplicates in my rows and combine the values from the 4 columns onto one line. Like so:
a 1
b 1
c 1
a 3
b 2
c 4
a 3
b 6
c 5
a 5
b 0
c 7
Needs to become:
a 1 3 3 5
b 1 2 6 0
c 1 4 5 7
Can anyone help? Thank you so much!
Perhaps something like this will help:
The first column of the array:
Array Enter (Ctrl + Shift + Enter)
Copy down
Formula:
Please Login or Register to view this content.
Generated array from above column:
Array Enter
Copy across and down.
Formula:Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Thank you newdoverman. I should clarify that the row values depicted in my example as a,b,c and d are actually thousands of different values. In your equation below you reference $A$2:$A$100=$D2 with $D2 representing value a.
=IFERROR(INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=$D2,ROW($A$2:$A$100)-1),COLUMNS($A$1:A1))),"")
Does this mean I need to create a unique list of each of the none duplicated items to reference for this value? I am not sure how to circle it back. (or if I am making any sense at all)
Thanks!
It doesn't matter how long your list is. The cell ranges would have to be increased to accommodate the data. To accommodate 100k rows the formulae will look like this:
Array formulae (enter with Ctrl + Shift + Enter)
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
I don't know how much data is involved but from the above, you should be able to see how the formulae need to be changed to accommodate a larger amount of data.
The first formula will create the unique listing of data for you.
Last edited by newdoverman; 10-23-2014 at 06:58 PM.
Thank you again. I am not fully understanding arrays and my original post didn't lay out properly. My data stretches across 4 columns and I cannot understand how to use the information you have given me to solve. I guess I will spend some time reading up on arrays tonight
Original Data
Untitled.png
Thanks for the prompt replies!
Re: Excel 2010 Move data from multiple columns/rows to single row
Here is one answer to you last layout.. Arrayed entered formula..
Formula:Please Login or Register to view this content.
Note:-
1. Read about the functions used.. and array formulas..
2. Use FORMULA AUDITING many times until you conclude anything..
that's the way you can learn ...
Check the attached file..
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
Seeing that you are having problems implementing a solution, if you upload a sample of your data (as realistic as possible without sensitive data) perhaps we can figure out what the problem is.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Sorry, this was a double post....don't know for sure what happened here.
Thank you again for the reply newdoverman.
Attached is a sample of what I am working with. The second tab shows what I need the output to look like. I appreciate you steering me in the right direction.
If I understand this correctly, your original data has several applications with many duplicates and there are 4 categories M1 to M4 for each of these applications.
Is there a limit on the number of times each application can appear in the original listing? If your supplied example is accurate, that would mean that there would be over 700 columns of data for application "A" or, Are you wanting a SUM for each application under each of the columns labelled M1, M2, M3, M4?
No that is not the case, sorry for the confusion. That is my actual file and I just removed the DATA and filled with letters. I wanted you to see how large it was. Each DATA element will only have at most 1 number for each of the 4 columns: M1 - M4. (some don't fill all of these rows)
There are a total of 5630 unique DATA elements. Many of these repeating several times thus filling M1- M4 for a total of 8474 total data elements.
Thanks for clarifying.
I think that this will do what you want.
To make it easier to see what I was doing, I made sure that there was only a max of 4 entries for each application. I re-entered numbers so that I could track errors if they were to occur. Then I moved the blocks of numbers from their original columns to be sure that the formula was correctly picking up the values.
It uses the same formula to determine the unique application names in column A. These formulae are set to handle up to row 10000.
Enter with Ctrl + Shift + Enter
Formula:Please Login or Register to view this content.
The second formula is a basic SUMPRODUCT formula to be entered in B2 and copied across and down.
Formula:Please Login or Register to view this content.
Last edited by newdoverman; 10-24-2014 at 04:53 PM. Reason: additional information
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks