0

Suppose I have similiar table [over 200 rows and over 80 columns], but with B column as additional description of "vendorname", and with others (column C and rightwards) filled with formulas returning either TEXT or "".

You wrote: "=IFERROR(INDEX(...),"") shows empty cell after displaying all data".

What, if I'd like formula to continue returning texts from the next rows (vendorname_1, vendorname_2 and so on)?

And I can't imagine, how to use MATCH in such a case.

I think of a formula that "knows": 'OK, there's no text in this row (vendorname_1) anymore, let's offset one down (vendorname_2) and egzamine it'.

I'd like to end up with three-column wide table, where in first two columns appropriate "vendorname" and "description of vendorname" are repeated as long, as there are text values (data) being returned by formula in the third for that particular "vendorname"...

Any help would be greatly appreciated! (And sorry for my english.) Best regards.

my table

Improve this question
5
  • Who is "you" in "you wrote"? What is the formula you are using? Post a data sample or a screenshot of your data and explain the desired result.
    – teylyn
    Commented Jan 29, 2017 at 23:10
  • It was my first post and I thought I'm asking Máté Juhász, who answered question by other user
    – Radian
    Commented Jan 29, 2017 at 23:33
  • see: superuser.com/questions/990125/…
    – Radian
    Commented Jan 29, 2017 at 23:33
  • I've attached screenshot of my table - link is on very end of my post (i.sstatic.net/O5XtV.gif)
    – Radian
    Commented Jan 29, 2017 at 23:36
  • Your description does not make much sense. There are no vendors in your screenshot. It's a bit presumptuous from you to expect us to root around in an old question to find the formula you are referring to.
    – teylyn
    Commented Jan 30, 2017 at 0:06

1 Answer 1

Reset to default
0

If you want to achieve what your screenshot shows, you'd do better without formulas. What you need is to unpivot your table.

There are ways to do that with Excel. Either use a reverse pivot or the Unpivot command in Power Query for Excel 2010 and 2013, also called Get and Transform in Excel 2016.

Improve this answer
2
  • Thank you a lot @teylyn! In fact, it was my second encounter with Power Query Editor (the first being only yesterday) - but I managed to achieve my goal! Nevertheless, I'm still curious if this is possible with formula...
    – Radian
    Commented Jan 30, 2017 at 1:28
  • Not really. You would have to roll your own with VBA.
    – teylyn
    Commented Jan 30, 2017 at 1:54

You must log in to answer this question.

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