Combining multiple columns into one data set

[ShineMagical]

Hi everyone,


I am trying to get the area highlighted in yellow to appear and refresh automatically when adding more data as I continue to add data in the future, while also not copying any blank spaces. https://imgur.com/cf6kvJu




Is there a way to do this with a formula?

 

[Special-K99]

You havent explained why the value in column A suddenly jumps from D to G, same with column B (E to H) so I'll have to assume...

Try

in A11
=IF(D11<>"","D"&ROW(),IF(G11<>"","G"&ROW(),""))
in B11
=IF(E11<>"","E"&ROW(),IF(H11<>"","H"&ROW(),""))

and copy down the columns

 

[ShineMagical]

You havent explained why the value in column A suddenly jumps from D to G, same with column B (E to H) so I'll have to assume...

Try

in A11
=IF(D11<>"","D"&ROW(),IF(G11<>"","G"&ROW(),""))
in B11
=IF(E11<>"","E"&ROW(),IF(H11<>"","H"&ROW(),""))

and copy down the columns

Ah. Thank you for the reply but I am afraid I wasn't clear enough in my OP.
Columns D and E are calculating a XIRR for an account. Columns G and H are also calculating a XIRR for a different account.

I am trying to have in columns A and B the XIRR calculated for all of the accounts in the spreadsheet, so I have to combine the data sets for all accounts and have the list refresh automatically once more data is entered into columns D/E/G/H.

As an aside, I've already tried referencing the cells in columns D/E/G/H but if the cell in those columns is blank the reference makes a zero value which messes up the XIRR calculation even if you disable showing zeros in the spreadsheet through the advanced options menu.