0

I have an xlsx report from a finance tool which provides raw numbers but none of the formatting or formulae I wish to use to better read and analyse the numbers.

The report is structured like so:

            Col1  Col2  Col3
Group1
       Row1 Val1  Val2  Val3
       Row2 Val1  Val2  Val3
Group2
       Row1 Val1  Val2  Val3

I want to be able to copy and paste this raw report into a sheet in Excel and have my augmented report pull the data in automatically using VLOOKUP (or any other method, this is just the way I’ve seen this kind of thing done before).

However, as you can see the rows are grouped and a row name can show up more than once in different groups. So a VLOOKUP on “Row1” for example would end up ambiguous as it could be the Row1 in Group1 or Group2.

Is there a way I can lookup this data in Excel without modifying the original report in any way? (e.g. you could copy and paste the group name, prepending to each row name like “Group1 - Row1”) I’d prefer to make this reporting process as quick and efficient as possible.

Macro solutions - while something I am capable of - are not really an option as they would be unmaintainable without myself.

Improve this question
3
  • What are you looking for and based on what? Are "Row1", "Row2" etc... values? Maybe a screencap of the spreadsheet and the expected result would be useful here
    – cybernetic.nomad
    Commented Mar 30, 2023 at 21:44
  • 1
    @Luke ,,, in general any Lookup command are not suitable with grouped data but you may try INDEX & MATCH with Column reference like A:A ,,,,, like one example =INDEX(C:C,MATCH("TV"&"EAST",A:A&B:B,0)) !!
    – Rajesh Sinha
    Commented Mar 31, 2023 at 4:19
  • I recommend Power Query to handle this. You could create a query to copy down the group names in the empty cells and delete empty data rows.. Then you could perform your analyses.
    – bugdrown
    Commented Jul 29, 2023 at 12:23

0

Reset to default

You must log in to answer this question.