#1
|
|||
|
|||
sorting cells in excel into specific columns
Ho all, wondering if anyone call tell me how to sort cells into appropriate columns? On the attached excel spreadsheet, I have a grocery list I copied and pasted from a website. Excel put all of the data in column A, but what I want to do is extract this data and sort it so that column A product name, would have the item description, and column B labelled item price, would show the unit price of the items, row by row. Is there any way to do this in excel using function and formulas? I know I can do it manually cell by cell, but that is time consuming. Many thanks
|
#2
|
|||
|
|||
Here you go!
|
#3
|
||||
|
||||
For just those two columns, and if you have O365 with the LET and SEQUENCE functions, then in any single cell:
Code:
=LET(rng,Sheet1!A1:A100,INDEX(INDEX(rng,SEQUENCE(ROWS(rng)/4,4)),SEQUENCE(ROWS(rng)/4),{1,3})) Code:
=LET(rng,A1:A100,INDEX(INDEX(rng,SEQUENCE(ROWS(rng)/4,4)),SEQUENCE(ROWS(rng)/4),{1,3})) |
#4
|
|||
|
|||
Unfortunately I don't have Office 365 so this won't work for me, but thanks for posting anyways
|
#5
|
|||
|
|||
Your solution works great so thanks for posting it here, but I don't understand why it works, I understand the offset function and the rows function, but the formula you provided leaves me scratching my head, wondering how the heck this all works. So if you have time, would you mine posting an explanation of why this formula works, and what specifically it is doing to make everything work correctly? If you prefer you can send me a private email instead. if you don't have time, I understand as well, and thank you again for the solution you provided.
|
#6
|
|||
|
|||
Let's analyze the formula in Sheet2!A2
Code:
OFFSET(Sheet1!$A$1;4*(ROW()-ROW($A$1))+0;0) 1) The anchor cell here is Sheet1!$A$1. (NB! This is an absolute reference, so the anchor cell is always same when the formula is copied down.); 2) (ROW()-ROW($A$1)) returns 1 when the formula is 2nd row, 2 when the formula is in 3rd row, etc., i.e. it returns the number of current datarange row for the cell with formula; 3) 4 is the number of rows used for single product in Sheet1. When you multiply it with datarange row number in sheet2, you get the number of row which is 1 less than according product name in sheet1; 4) Adding 0 to previous number gives the number which you have to add to row number (1) of anchor cell to get the row number of product name from sheet1, With this, the 2nd parameter of OFFSET formula <4*(ROW()-ROW($A$1))+0> is covered - you get values from Sheet1 rows 5, 9, 13, etc.; 5) As all data in Sheet1 are in column A, the last parameter will be always 0. To get quantity values, you have to get values one cell below product names. For this all remains same as above, except in p. 4) you have to replace 0 with 1. And for ItemPrice 0 with 2, etc. |
#7
|
|||
|
|||
Thank you for taking the time to post an explanation. i will review it, and close this thread as resolved
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How can I transfer specific data from email to specific excel cells? | Mauro | Outlook | 1 | 11-11-2019 09:17 PM |
Linking Specific text fields in PP to specific cells in an Excel table | GWRW1964 | PowerPoint | 0 | 02-26-2018 07:37 AM |
Linking cells: can sub-cells move with their master-cell when sorting? | kw01 | Excel | 1 | 06-30-2015 05:02 PM |
Macro to copy specific columns in Excel from another spreadsheet | KD999 | Excel Programming | 1 | 07-20-2012 08:58 AM |
Sorting columns in Excel - please advise | Jonre | Excel | 2 | 08-21-2009 02:38 AM |