Sort multiple columns of same type data based on cell values

XCitableBill

New Member
Joined
Apr 13, 2020
Messages
11
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hello Forum.
I have a workbook of 5 individual sheets. Each sheet has the same data headings, but the values within the cells of each sheet may be different. Here's a rough example:

ItemNoPriceItemNoPriceItemNoPriceItemNoPrice ItemNoPrice
119.99119.99119.99119.9974.00
27.0027.0044.0027.00116.00
44.0068.00106.00750.002510.00
12110.0099.001111.001310.004410.00

Each series of data (ItemNo, Price, Qty) is from a different worksheet. I want to sort the data by ItemNo in Ascending Order so that the data above would look like the following:

ItemNoPriceItemNoPriceItemNoPriceItemNoPriceItemNoPrice
119.99119.99119.95119.99
27.0027.0027.00
44.0044.00
68.00
74.0074.00
99.00
106.00
1111.001111.00
12110.00
1310.00
2510.00

I was able to get the first two columns to sort correctly, but I have 5 series of data points. I run into trouble when the lowest ItemNo in the row is in columns 3 -5. I've come to the apex of my vba knowledge. I don't know how to compare the five ItemNo values to figure out which ones are the lowest value for that row, then insert blank cells in the other rows that have a larger value until that value is the lowest value in the row and so on and so forth. I'm thinking there's got to be a better way than calling a number of if..then...else or For statements or With statements.
Thanks in advance.

Bill
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

XCitableBill

New Member
Joined
Apr 13, 2020
Messages
11
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hello Forum.
I have a workbook of 5 individual sheets. Each sheet has the same data headings, but the values within the cells of each sheet may be different. Here's a rough example:

ItemNoPriceItemNoPriceItemNoPriceItemNoPriceItemNoPrice
119.99119.99119.99119.9974.00
27.0027.0044.0027.00116.00
44.0068.00106.00750.002510.00
12110.0099.001111.001310.004410.00

Each series of data (ItemNo, Price, Qty) is from a different worksheet. I want to sort the data by ItemNo in Ascending Order so that the data above would look like the following:

ItemNoPriceItemNoPriceItemNoPriceItemNoPriceItemNoPrice
119.99119.99119.95119.99
27.0027.0027.00
44.0044.00
68.00
74.0074.00
99.00
106.00
1111.001111.00
12110.00
1310.00
2510.00

I was able to get the first two columns to sort correctly, but I have 5 series of data points. I run into trouble when the lowest ItemNo in the row is in columns 3 -5. I've come to the apex of my vba knowledge. I don't know how to compare the five ItemNo values to figure out which ones are the lowest value for that row, then insert blank cells in the other rows that have a larger value until that value is the lowest value in the row and so on and so forth. I'm thinking there's got to be a better way than calling a number of if..then...else or For statements or With statements.
Thanks in advance.

Bill
I only posted ItemNo and Price.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,995
Messages
5,543,185
Members
410,584
Latest member
Bluefox68
Top