Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-08-2022, 07:28 AM
oscarlimerick oscarlimerick is offline sorting cells in excel into specific columns Windows 8 sorting cells in excel into specific columns Office 2013
Advanced Beginner
sorting cells in excel into specific columns
 
Join Date: Jul 2020
Posts: 57
oscarlimerick is on a distinguished road
Default 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
Attached Files
File Type: xlsx for microsoft excel forum.xlsx (10.3 KB, 9 views)
Reply With Quote
  #2  
Old 05-09-2022, 12:03 AM
ArviLaanemets ArviLaanemets is offline sorting cells in excel into specific columns Windows 8 sorting cells in excel into specific columns Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Here you go!
Attached Files
File Type: xlsx ListToTableExample.xlsx (12.5 KB, 9 views)
Reply With Quote
  #3  
Old 05-10-2022, 01:27 AM
p45cal's Avatar
p45cal p45cal is offline sorting cells in excel into specific columns Windows 10 sorting cells in excel into specific columns Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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}))
and if this is on the same sheet there's no need to refer to the sheet name:
Code:
=LET(rng,A1:A100,INDEX(INDEX(rng,SEQUENCE(ROWS(rng)/4,4)),SEQUENCE(ROWS(rng)/4),{1,3}))
These formulae will spill down and across so make sure there's nothing in those cells else you'll get #SPILL! in the cell with the formula.
Reply With Quote
  #4  
Old 05-10-2022, 06:05 PM
oscarlimerick oscarlimerick is offline sorting cells in excel into specific columns Windows 8 sorting cells in excel into specific columns Office 2013
Advanced Beginner
sorting cells in excel into specific columns
 
Join Date: Jul 2020
Posts: 57
oscarlimerick is on a distinguished road
Default

Unfortunately I don't have Office 365 so this won't work for me, but thanks for posting anyways
Reply With Quote
  #5  
Old 05-10-2022, 06:08 PM
oscarlimerick oscarlimerick is offline sorting cells in excel into specific columns Windows 8 sorting cells in excel into specific columns Office 2013
Advanced Beginner
sorting cells in excel into specific columns
 
Join Date: Jul 2020
Posts: 57
oscarlimerick is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
Here you go!
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.
Reply With Quote
  #6  
Old 05-11-2022, 02:08 AM
ArviLaanemets ArviLaanemets is offline sorting cells in excel into specific columns Windows 8 sorting cells in excel into specific columns Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Let's analyze the formula in Sheet2!A2
Code:
OFFSET(Sheet1!$A$1;4*(ROW()-ROW($A$1))+0;0)
Offset returns a value a number of rows and columns from anchor cell;
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.
Reply With Quote
  #7  
Old 05-11-2022, 07:26 AM
oscarlimerick oscarlimerick is offline sorting cells in excel into specific columns Windows 8 sorting cells in excel into specific columns Office 2013
Advanced Beginner
sorting cells in excel into specific columns
 
Join Date: Jul 2020
Posts: 57
oscarlimerick is on a distinguished road
Default

Thank you for taking the time to post an explanation. i will review it, and close this thread as resolved
Reply With Quote
Reply

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
sorting cells in excel into specific columns Linking cells: can sub-cells move with their master-cell when sorting? kw01 Excel 1 06-30-2015 05:02 PM
sorting cells in excel into specific columns 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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:06 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft