Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-22-2020, 06:26 PM
Ricko_uk Ricko_uk is offline Extracting data from a column into another but sorted and with 2 related cells next to it Windows 10 Extracting data from a column into another but sorted and with 2 related cells next to it Office 2019
Novice
Extracting data from a column into another but sorted and with 2 related cells next to it
 
Join Date: Aug 2020
Posts: 10
Ricko_uk is on a distinguished road
Default Extracting data from a column into another but sorted and with 2 related cells next to it

Hi,
first time posting here so hopefully this is the right area.

Please see the attached spreadsheet with sample data. What I need to do is the following:

1) only paste data into the first sheet (formatted as already shown in there) and Excel extract data from Sheet 1 to fill in Sheet 2 and Sheet 3

2) Sheet 2 contains only 3 of the 5 columns of Sheet 1: MyIndex, Serial Number and Sales. It is sorted by Sales LOW to HIGH. And next to each Sales cell it has the related Serial Number and MyIndex value.

3) Sheet 3 is the same as Sheet 2 (explained in point 2 above) but: 1) instead of Sales it contains the Revenue column and 2) the Revenue Column is sorted from HIGH to LOW

NOTE: If it helps I can make sure that the data pasted on Sheet 1 is always a known/fixed number of rows

If possible:
- not using extra columns/sheets (but if cannot be avoided then that's ok)
- to do it with formulas and not macros/programming



Many thanks
Attached Files
File Type: xlsx Book4.xlsx (10.9 KB, 7 views)
Reply With Quote
  #2  
Old 08-22-2020, 11:49 PM
Purfleet Purfleet is offline Extracting data from a column into another but sorted and with 2 related cells next to it Windows 10 Extracting data from a column into another but sorted and with 2 related cells next to it Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

If i have read this right there are a few options, the old skool formula works but you need to be careful with how many rows are input and output and duplicates make it more complex.

Do a Small on the sales column =SMALL(Sheet1!$D$2:$D$5,ROWS($A$2:A2))

then index/match to bring back the serial and index numbers

Probably the easiest solution is to use pivot tables but you need to put the sorted column first, but then updating could be as easy as a refresh

Then we have the new dynamic arrays which i think are only in Excel 365, but they are very cool - Filter With a sort =FILTER(SORT(Sheet1!A2:E5,4,1),{1,1,0,1,0})

See attached
Attached Files
File Type: xlsx Book4_Purfleet.xlsx (19.5 KB, 5 views)
Reply With Quote
  #3  
Old 08-24-2020, 02:48 PM
Ricko_uk Ricko_uk is offline Extracting data from a column into another but sorted and with 2 related cells next to it Windows 10 Extracting data from a column into another but sorted and with 2 related cells next to it Office 2019
Novice
Extracting data from a column into another but sorted and with 2 related cells next to it
 
Join Date: Aug 2020
Posts: 10
Ricko_uk is on a distinguished road
Default

Thank you, I used that for part of the solution
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extracting data from a column into another but sorted and with 2 related cells next to it Extracting unique values from a list and sorted alphabetically Marcia Excel 2 10-13-2018 11:25 PM
eliminating blan rows between cells in a column cantaining data FUGMAN Excel Programming 6 03-01-2017 07:35 AM
Extracting data from a column into another but sorted and with 2 related cells next to it Keeping the source format when extracting data from multiple cells using Vlookup function praboos2001 Excel 6 11-15-2016 04:40 AM
Extracting data from a column into another but sorted and with 2 related cells next to it Inserted Pictures from Local Data to cells column in excel jessicakencana Excel Programming 1 03-26-2016 03:51 AM
Extracting data from a column into another but sorted and with 2 related cells next to it Lookup a value from non-sorted data udea Excel 12 04-25-2011 04:34 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:58 PM.


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