Is there a formula that I can use to reorganize data from multiple rows into multiple columns on the same row?

Question

I am trying to manipulate data in a large spreadsheet (~1,500 rows).

Some invoice entries only have one WBS assigned to them, some have upwards of 8.

If an invoice has more than one WBS assigned to it, I want them all combined on the same row, rather than each on a separate row as they are now.

I have an example below of what my data looks like in the first image.

I would like it reorganized to match the second picture.

For example, in the first picture A3:F3 contains the first WBS entry for Supplier B, Invoice 2. The 2nd WBS for that invoice is in E4:F4 and the 3rd is in E5:F5.

In the second image, all 3 of those WBS entries are combined on the same row in A3:F3.

Answer 1

EDIT for clarification.

Looks like you want to shift data to later columns that doesn't have leading column information. I believe this is in order to allow you to sort the spreadsheet.

If your goal is simply to be able to look at data in more detail, you might just fill in the first few columns so that you could use a Pivot Table to analyze data.

If there is a reason you actually want to transpose the data instead of using a Pivot Table, there are probably a lot of ways.

The simple one (in terms of basic formulas) is to add a formula like this to cell G2:

=IF($D3="",E3,"")

(This tells Excel: if there is nothing in cell D3, then put E3 in cell G2.) You would pull the formula across out as far as you needed to transpose (not sure how many different WBS Amounts you have for any particular Invoice Amount WBS Number)

To use an example from your spreadsheet, let's look at the data you have. You want to transpose the number in E4 to G3. Your formula (once pulled down from G2) would give you the result you want. The data related to each supplier, invoice, and so forth would be laid out in later columns of the spreadsheet versus below your data.

You still can't sort the data until you copy the formula columns and then paste special values so that the formulas don't recalcuate if you sort.

You will no longer need the rows without column A - D data, so you should sort by Column A and delete any rows you don't need.

Again, the best solution MIGHT be to start with a spreadsheet that has all columns filled out, normally, as you can just use a pivot table to get data into this format.