Needing to transpose a bunch of numbers??????
I have two columns of data. In column A I have customer numbers, in column B I have equipment ID's that belong to that customer. I need the equipment ID's to go horizontally and match up with the customer number. I have given an example of the way I need it to look in row 3 of this picture. Any help would be greatly appreciated.
Excel Challenge.png
Last edited by Restiphani; 01-15-2018 at 11:09 AM. Reason: Adding Attachment
Welcome to the forum!
Sounds like a Power Query job to me. Attach the workbook here. We can't work with an image!
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Spreadsheet should be attached in the original message now.
Thanks
You can do this with a couple of formulae. Put this formula in C2:
=IF(B2="","",A2&"_"&COUNTIF(A$2:A2,A2)-1)
and copy down to the bottom of your list of data. It will tag a unique serial number onto the end of each Location Number for those records where the Equipment number is not blank.
Then you can use this formula in cell E2:
=IF($B2="",IFERROR(INDEX($B:$B,MATCH($A2&"_"&COLUMNS($E:E),$C:$C,0)),""),"")
This can be copied across and down as required, and will return each equipment number in turn for the Locations where there is no equipment (i.e. on the first record for each Location).
The attached file demonstrates this, but I have only copied the formulae down to row 114, and across to column M - you will see that the block of records from rows 29 to 43 and from 74 to 103, for example, will require the formula in column E to be copied across further, in order to display all the relevant Equipment numbers (14 and 29 respectively).
Hope this helps.
Pete
I wish I could give you a hug ...... You know how much time you have saved me.
You're welcome - thanks for the rep.
If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks