+ Reply to Thread
Results 1 to 18 of 18

Reorganising data from A>1,2,3 to 1>A,D,E.

  1. #1
    Registered User
    Join Date
    04-16-2019
    Location
    London
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    56

    Reorganising data from A>1,2,3 to 1>A,D,E.

    Hello

    I am sure this has been raised before but I have no idea what to search for. I will do my best to explain what I am trying to do. (let me know if I should post this in another location).

    I'll use arbitrary data for the explanation.

    Lets say I have 10 columns, each has a letter label (A,B,C,D etc)

    Each column is full of random (non sequential) numeric values (103,223,556,773,101)

    The are many multiples listings for the numeric values in different columns, so 103 may be in B E and L. 809 may be in A, B, C and E.

    I am trying to take this data and list all of the numeric values in a sequential list with the next column listing what Letter values they are associated with.

    So, if 809 was listed in the columns A, B and E I wanted organise the data so it looks like

    809 | A,B,E


    Is this possible to do? I should point out that the numbers are product codes (letters and numbers) and the letter values are 'categories' for a website.

    Thanks

    Jon

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    Hi & welcome to the board.
    Maybe something like
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-16-2019
    Location
    London
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    56

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    Hi Fluff

    Thanks for the quick response.

    Honestly, I only have a basic understanding of using formula in Excel (I can do Vlookups and basic IFs and ANDs etc). Please could you explain your solution in a little more 'layman' detail?

    Much appreciated.

    Jon

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    Best thing is to get it working first, can you supply a sample workbook.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    04-16-2019
    Location
    London
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    56

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    Have a look at this example spreadsheet I made.

    I have a ton of data in this approximate format (on the left) and I want to reorganise it so it is displayed with the 'product number' (1245k etc - not real no.) in alphabetical order and displayed as shown on the right.

    Regards

    Jon
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    Ok, one mod to account for the blanks cells
    Please Login or Register  to view this content.
    This needs to go in a regular module
    And you will need to add a new sheet called Sheet2

    To see how to add & run macros have a look here https://www.contextures.com/xlvba01.html

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    Another way:

    A
    B
    C
    D
    E
    F
    G
    H
    1
    Equipment Accessories Parts Vehicles
    2
    234k/1 29145/w 1235/5 187/gs 234k/1 Equipment,Accessories,Vehicles G2: {=catif((MMULT({1,1,1,1}, --($A$2:$D$5 = F2))), $A$1:$D$1)}
    3
    1235/5 234k/1 187/gs 29145/w 1235/5 Equipment,Parts
    4
    187/gs 29145/w 45557/kl 1116/67 Parts
    5
    29145/w 1116/67 234k/1 29145/w Equipment,Accessories,Parts,Vehicles


    Please Login or Register  to view this content.
    I believe the CatIf function could be replaced with TEXTJOIN, but my version of Excel doesn't support it.
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Registered User
    Join Date
    04-16-2019
    Location
    London
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    56

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    I think I have thought of a better way to describe what I am trying to do.

    Lets say I have two worksheets.

    In worksheet 1 I have a full list of every product code (1,2,3,4,5,6,7,8,9 e.g.)

    In worksheet 2 I have columns with different "titles" (a text word in the top cell) under which are a selection of product codes relevant to that title (all of which will be listed somewhere in worksheet 1).

    Is there a way to do a VLookup (or sorts) in worksheet 1 that will look in a given column (in worksheet 2) and if it finds a match it will return the 1st cell text value (from column in worksheet 2) and show that value in the cell next to the searched for code from worksheet 1?

    Does that make more sense?

    Sorry if not

    Thanks

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    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.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    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.

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    Does that make more sense?
    Nope.
    Is the workbook that you supplied representative of your data?
    Have you tried either of the two options provided?

  11. #11
    Registered User
    Join Date
    04-16-2019
    Location
    London
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    56

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    Quote Originally Posted by AliGW View Post
    Will you please attach a sample Excel workbook?

    Thanks Ali for the advice, I am new to this!

    I have attached an example document which I hope has sufficient representative (desensitised) data to explain my quandary. Let me know if the sample size is too small.

    The list in worksheet 1 is the result I want to end with, the data in worksheet 2 is how my data is currently listed. I should point out that I do have a full list of part numbers, I just don't have the combined category IDs associated with each one in the format shown in the document.

    Thanks, I appreciate any help.

    Jon
    Attached Files Attached Files
    Last edited by AliGW; 04-17-2019 at 06:03 AM.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    Do the categories really need to be concatenated? Could they be in separate columns? If not, are you happy for helper columns?

  13. #13
    Registered User
    Join Date
    04-16-2019
    Location
    London
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    56

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    They don't have to be in the same column, but I understand the concatenate function (roughly), so I assume that would be a simple fix if they were in separate columns to the right of the main product list.

    Thanks again

  14. #14
    Registered User
    Join Date
    04-16-2019
    Location
    London
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    56

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    Is it possible to use the VLookup (or HLookup?) function to look for a value in a column, then if it finds it return a specific cell from that column (the top cell in this case)? If this is possible, is it possible to add a rule that does this for every column in a target range?

    I hope I am not speaking too much gibberish

    Regards

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    I'm not asking you to work out how to do it - that's what we'll do for you. I just want to know the answers to my questions. So, we can use helper columns - yes?

  16. #16
    Registered User
    Join Date
    04-16-2019
    Location
    London
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    56

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    Hi Ali

    Yes that would be fine.

    Thanks

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    Great. I am sure someone who's around this afternoon will take a look.

  18. #18
    Registered User
    Join Date
    04-16-2019
    Location
    London
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    56

    Re: Reorganising data from A>1,2,3 to 1>A,D,E.

    *update

    Thought i'd let you guys know that I figured out how to do this (as I have thought about nothing else the last 24 hours lol)

    I used the =textjoin and =if functions together. Here is an example:

    =TEXTJOIN(",",TRUE,IF($A$2:$AD$300=AF2,$A$1:$AD$1,""))

    This allowed me to do exactly what I needed (per worksheet), then I just populated a dozen or so columns in my target sheet then 'textjoined' the columns together.

    Thanks for the help.

    Regards

    Jon

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 34
    Last Post: 01-21-2014, 03:07 PM
  2. Reorganising data in a spreadsheet
    By Mr_Natural in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-29-2013, 08:40 AM
  3. [SOLVED] reorganising large amounts of excel data
    By geologist in forum Excel General
    Replies: 10
    Last Post: 06-15-2012, 10:43 AM
  4. Reorganising data into another worksheet format
    By elkie in forum Excel General
    Replies: 0
    Last Post: 06-17-2009, 03:03 AM
  5. Help Reorganising / Presenting My Data
    By dvent in forum Excel General
    Replies: 6
    Last Post: 11-05-2008, 03:13 PM
  6. reorganising data
    By zvonul in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-10-2008, 05:51 AM
  7. [SOLVED] Reorganising data - macro needed?
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-19-2006, 12:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1