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:
...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 |
|---|
|
|---|
| A | B | C | D | E | F | G | H |
|---|
| 1 | a | b | c | d | e | f | g | helper |
|---|
| 2 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 0 |
|---|
| 3 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 0 |
|---|
| 4 | 4 | | | | 8 | 9 | 10 | 3 |
|---|
| 5 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 0 |
|---|
| 6 | 6 | 7 | 8 | | | | | 4 |
|---|
|
|---|
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.
