1

I've searched for a similar question here, but haven't been able to find something that answers my issue.

I'm a mediocre user of Excel 2010 with no experience in macro's. I have a dataset where each row represents a data entry. Let's say each row can be for each of its values (the columns) the maximum or minimum of the entire dataset. How can I create a row at the top where the, for instance, maximum row is shown dynamicly? So when extra data is added to the bottom of the dataset, the new maximum (if applicable) is shown in that row at the top.

enter image description here

So, in this example above, if for instance Brad is added, and Brad has bought for 40 euros of bread and 20 euros of wine, his row is shown/copied/duplicated to row 2, like so:

enter image description here

Improve this question
1
  • I'm not an expert here, but do you have much knowledge of pivot tables? Currently there is a good video (1 of 3) on YouTube
    – Dave
    Sep 18, 2012 at 10:20

1 Answer 1

Reset to default
1

EDITED ANSWER:

Please try this: Where A1 is:

=INDEX(A4:A10;MATCH(B1;B4:B10;0))

and B1 is:

=MAX(B5:B7)

enter image description here

OLD ANSWER:

I am not sure if I understand you correctly but it sounds like

=max(A1:A20)

or

 =min(A1:A20)

is what you need to get the max/min values for your row, where your row starts at A1 and ends at A20. If you add new values to eg. A21 you would need to alter that forula or you could also change the formula to

=max(A1:A1000)

for eg. It is not necessary that you have values in all these cells.

EDIT: Oh i see your problem now but I need to leave. I will reply you later if theres no one faster ;) Maybe have a look at vlookup meanwhile

Improve this answer
3
  • Thanks for your answer, but I don't need just the max. or min. I need an entire row to be copied because of one max. or min. value in that row (the question is extended with an example now).
    – TimothyHeyden
    Sep 18, 2012 at 9:55
  • Edited my answer. tell me if you have problems.
    – Langhard
    Sep 18, 2012 at 12:11
  • It does the job perfectly, and gives me also the opportunity to do that match in the other columns as well! Thank you!
    – TimothyHeyden
    Sep 18, 2012 at 12:31

You must log in to answer this question.

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