0

Below is a sample of a spreadsheet I have (fig 1). It has been exported from an asset tracking system and provides me a list of information about the hardware we have.

Each asset "area" uses the same format (Name/Asset/Date/Model/Serial) before repeating and I need to get them into columns rather than rows (see fig 2) for entry into a new spreadsheet.

I'm in the process of writing a macro using my Razer keyboard software (involving a lot of copying, moving between workbooks, and pasting) but does Excel actually do that itself?

enter image description here enter image description here

Improve this question
2
  • Have you tried TRANSPOSE(), or the transpose option in copy+paste special?
    – Kyle
    Commented Sep 28, 2015 at 19:36
  • It can be done pretty simply, and quickly, using VBA, if that is an option for you.
    – Ron Rosenfeld
    Commented Sep 28, 2015 at 20:33

1 Answer 1

Reset to default
-1

As long as all your fields on your original sheet are always in the same order you can use this:

=offset(index(Sheet1!$B:$B,match($A2,Sheet1!$B:$B,0)),column(B$1)-1,0)

Put that in B2 and autofill both down and across. It uses INDEX/MATCH to find the part number, then OFFSET, using the current column number, to find the appropriate field. The OFFSET bit doesn't use the name of the field you're looking for, which is why they have to always be in the same order.

ETA: Forgot to mention, this formula assumes your part names are already in column A.

Improve this answer
1
  • Care to explain the downvote?
    – Kyle
    Commented Sep 29, 2015 at 10:55

Not the answer you're looking for? Browse other questions tagged .