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

tombull89
  • 6,699

1 Answers1

-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.

Kyle
  • 2,436