Can I move data from rows into columns?

[sari.becker]

I have a file with three column headers ORGANIZATION, EVENT CODE, and AMOUNT. There are 19 rows, with six different organizations and seven different event codes. I want to move the event codes and amounts to the same row as the organization, adding extra columns for the different event codes. (See attached file for clarification.) The file has two worksheets, one called "I HAVE" and one called "I WANT". The first is what my data looks like, the second is what I need.

I've tried a few different things, but I can't figure it out. I was leaning towards SUMIF, but not sure how to do that with so many columns.

Can someone please help?

Thanks in advance, much appreciated!
Sari

HELP 1.xlsx

[FDibbins]

Hi, welcome to the forum

If you already have the row and column headings, put this in sheet 2 B2, and copy down and across as needed...
=IFERROR(INDEX('I HAVE'!$C$2:$C$20,MATCH('I WANT'!$A2&'I WANT'!B$1,INDEX('I HAVE'!$A$2:$A$20&'I HAVE'!$B$2:$B$20,0),0)),"")

[sari.becker]

Oh my goodness! You're amazing! Thank you so much!!!

[sari.becker]

Hmm... there seems to be a problem. In my example sheet 'I HAVE', see how for Org B there are five donations for LN2013? Well, the formula seems to only be including the first one. Is there a way around that?

Thanks in advance!

[jjhayes]

Try

Please Login or Register  to view this content.

in Cell B2 on the I WANT sheet...copy across and down as needed.

Does that work?

[sari.becker]

Yes, that worked perfect! Thank you so much for your help!

I've added more info to my example... can you tell me how to do it now please? Again, I have the two sheets, 'I HAVE' and 'I WANT'.

Thanks so much!

Sari

HELP 2.xlsx

[oeldere]

with a pivot table.

see the attached file.

[sari.becker]

Thanks. What about if I have 500 IDs, names and addresses on sheet1, and 50 IDs and names on sheet2. I need the addresses to be filled in on sheet2. I can use the ID to match them. What formula would I use for this?

[oeldere]

I apreciate is you reply if my pivot table solution is usefull.

Answer to your question use index / match.

[sari.becker]

Pivot Table would work if I knew how to use it. I lost my addresses so now I need to fill them in. I tried index/match and it didn't work when I copied the formula down.

[oeldere]

in which file did you tried that?

post an example on the forum.

[sari.becker]

I tried in io my working file. I don't have time to convert it to an example that doesn't show sensitive data.

Thanks for your help... but I'm still stuck.

[sari.becker]

Try

Please Login or Register  to view this content.

in Cell B2 on the I WANT sheet...copy across and down as needed.

Does that work?

This worked exactly as I wanted it to... but when I tried it on a different sheet, the formula only worked in one column and not the rest...? I can't see why. Can you please help?

Thanks in advance.HELP 3.xlsx

[FDibbins]

I did not notice you had multiple entries, sorry. Try this instead...
=SUMIFS('I HAVE'!$C$2:$C$20,'I HAVE'!$A$2:$A$20,'I WANT'!$A2,'I HAVE'!$B$2:$B$20,'I WANT'!B$1)

[FDibbins]

Try this on your new file...
=SUMIFS(ORGANIZATIONS!$D:$D,ORGANIZATIONS!$B:$B,$B2,ORGANIZATIONS!$C:$C,ORGANIZATIONS!C$2&"*")

[sari.becker]

Try this on your new file...
=SUMIFS(ORGANIZATIONS!$D:$D,ORGANIZATIONS!$B:$B,$B2,ORGANIZATIONS!$C:$C,ORGANIZATIONS!C$2&"*")

hmmm... that gives me $395 in C2 (which is correct), but it also gives me $395 in E2, F2, G2, etc. There should only be $395 in that row.

[FDibbins]

try this...
=SUMIFS(ORGANIZATIONS!$D$2:$D$21,ORGANIZATIONS!$A$2:$A$21,$A2,ORGANIZATIONS!$C$2:$C$21,C$1&"*")

Note also that a few of your headings on sheet2 have a O ("oh") instead of 0 (zero)

[sari.becker]

Thanks again, so much for your help with this!

I have another question, something that seems like it should be so simple, but I cannot figure it out. I have 500 IDs, names and addresses on sheet1, and 50 IDs and names on sheet2. I need the addresses from sheet1 to be filled in on sheet2, assuming that the 50 on sheet2 do appear and match IDs on sheet1. So basically I just want to grab info from column C based on a match in column A, and have it appear in a new cell on sheet2.
I tried index and match, but coudln't figure it out. I tried replace and substitute. I don't know how to do this.

Please help. Thanks so much in advance!

[sari.becker]

Try

Please Login or Register  to view this content.

in Cell B2 on the I WANT sheet...copy across and down as needed.

Does that work?

Thanks again, so much for your help with this!

I have another question, something that seems like it should be so simple, but I cannot figure it out. I have 500 IDs, names and addresses on sheet1, and 50 IDs and names on sheet2. I need the addresses from sheet1 to be filled in on sheet2, assuming that the 50 on sheet2 do appear and match IDs on sheet1. So basically I just want to grab info from column C based on a match in column A, and have it appear in a new cell on sheet2.
I tried index and match, but coudln't figure it out. I tried replace and substitute. I don't know how to do this.

Please help. Thanks so much in advance!

[jjhayes]

I think the following would work. I will try to check this thread again tomorrow.

Please Login or Register  to view this content.

Let's assume you want the addresses in column D on your Sheet2 and the IDs are in Column A on Sheet2 (IDs are in Column A of Sheet 1 and addresses are in Column C of sheet 1), then paste the above in D1 and copy down.

Would need to be modified in the above assumptions aren't correct.

[sari.becker]

I think the following would work. I will try to check this thread again tomorrow.

Please Login or Register  to view this content.

Let's assume you want the addresses in column D on your Sheet2 and the IDs are in Column A on Sheet2 (IDs are in Column A of Sheet 1 and addresses are in Column C of sheet 1), then paste the above in D1 and copy down.

Would need to be modified in the above assumptions aren't correct.

FANTASTIC!!! Exactly what I was looking for! I was able to get it with VLOOKUP, but then I had to change the column number with each new column, which is annoying. Your solution is much better! Thank you!