210 columns to 7

PIsabel

Board Regular
Joined
Feb 4, 2014
Messages
126
Office Version
  1. 365
Platform
  1. Windows
Hello.
I have to copy 30 sets of 7 columns from the "Compounds" sheet and paste the values into the "Components" sheet.
The data is between rows 1 and 1000 (the first set = A1:G1000)

the first set in cell A1:G1000
the second set in cell A1001:G2000
the third set in cell A2001:G3000
and so on.
Basically I have to put all the columns in the same 7 columns.
Finally, I need to sort the data in ascending order.
Not all cell sets have data and most rows are unused. However, all cells have formulas
Someone help me?
_10076.jpg
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
What version of Excel are you using? Please update your account details to show that, as the version makes a big difference when developing a solution.
 
Upvote 0
What version of Excel are you using? Please update your account details to show that, as the version makes a big difference when developing a solution.Done.365
Microsoft® Excel® 2021 MSO (versão 2402 Build 16. 0. 17328. 20124) 32-bit
 
Upvote 0
If you are using 2021 why does your profile say 365?
 
Upvote 0
the first set in cell A1:G1000
the second set in cell A1001:G2000
the third set in cell A2001:G3000
It appears that all of your data on the Compounds worksheet are already in the same columns. If you only want the values, rather than the formulas, select the upper left cell on the Compounds worksheet and then Shift-Ctrl-End to select the entire range of data. Copy to the clipboard. Then go to the Components worksheet and select the upper left cell in the top row where you want the results to appear (I suggest selecting a cell in Row 2 so that Row 1 is available to add some column headers. Then use Paste Special > Values. That preserves only the values, not formulas. Next, add some column headers into the empty cells in the first row. Add a column label called "helper" to the column right beside the 7th column of data. You'll need a helper formula to check whether the data rows are completely blank. Assuming this helper column is column H, with the "helper" header in H1, enter this formula in H2:
Excel Formula:
=COUNTBLANK(A2:G2)
...you should see a number indicating the number of cells that are blank in the data table. Copy this formula to the clipboard and hit Shift-Ctrl-End...the Shift indicates that a selection should begin at that point and Ctrl-End is the keyboard shortcut for jumping to the bottom of the range. This should select all cells between the upper one where you've entered the COUNTBLANK formula and the end of the data. Then Paste the formula from the clipboard into all cells in the helper column. Completely blank rows will have a value of 7 (the number of columns in the A:G range).
MrExcel_20240311.xlsx
ABCDEFGH
1abcdefghelper
223456780
334567890
4489103
55678910110
66784
Components
Cell Formulas
RangeFormula
H2:H6H2=COUNTBLANK(A2:G2)

Now select a cell in the top row (where the column headers are) and on the Sort & Filter submenu, choose Filter, which will insert dropdown arrows that open a filter menu. Open the column filter for the helper column and uncheck All, then recheck the "7" so that only completely blank rows will be visible. Then select them (again, select the uppermost blank row and Shift-Ctrl-End to select all of them)...and right click, Delete Row. That deletes all blank rows and consolidates the table. Now open the filter in the helper column and Select All of the remaining options to show your consolidated data table. You can then delete the helper column if desired.

From there you can filter/sort the columns using the filter buttons in the column headers.

I'm not sure what the image you posted is showing, but it doesn't appear to be consistent with your description that all source data are in columns A:G somewhere.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top