Getting the data from various rows and reporting it in one row

X

xlbaby

New Member
Joined
Nov 5, 2012
Messages
39
Hi,

Need your help guys.


Can you please check the attached sheet and tell me whether is there a formula where I can pull the numbers from result tab and get it reflected on the first tab?

I was thinking if a formula can do this instead of any macros. Thanks a lot for your help.

The link to dropbox is here.

Dropbox - To Upload.xlsx
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Most people don't like to download spreadsheets from unknown sources , viruses etc. It's easy enough to get data from other sheets within your workbook, you just need to reference the data correctly like so

=Sheet1!A1

If your sheet is called result reference the cells like this to display the contents of A1 on the result tab into whatever sheet you want

=result!A1
 
Upvote 0
MrTeeny, thank you for your response. However it is not simple as referencing it. I will try to explain what I need.

I have 2 tabs: Sheet 1 ( where I want the results to be populated) and Result (from where the data needs to be pulled)

Please see below for result tab:
[TABLE="width: 3399"]
<colgroup><col span="23"><col></colgroup><tbody>[TR]
[TD]Unique ID[/TD]
[TD]Unique ID_E[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Interest Paid[/TD]
[TD]Interest received[/TD]
[TD]Federal Link Worksheet - Estimated Tax Penalty 2016[/TD]
[TD]Federal Link Worksheet - Late Filing Penalty 2016[/TD]
[TD]Federal Link Worksheet - Late Payment Penalty 2016[/TD]
[TD]Federal Link Worksheet - Late Payment Interest 2016[/TD]
[TD]January 1, 2016 Balance[/TD]
[TD]December 31, 2016 Balance[/TD]
[TD]State Name 2016[/TD]
[TD]Credit card number[/TD]
[TD]State Interest Paid[/TD]
[TD]State Interest received[/TD]
[TD]State Balance[/TD]
[TD]Penalty [/TD]
[TD]Interest[/TD]
[TD]Checking Account[/TD]
[TD]Savings Account[/TD]
[TD]Foreign Account[/TD]
[TD]Foreign Account balance[/TD]
[TD]Resident State[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2016:123456:V1[/TD]
[TD]ABC[/TD]
[TD]DEF[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$4,215[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$31,818[/TD]
[TD="align: right"]$27,603[/TD]
[TD]New Jersey[/TD]
[TD]5300[/TD]
[TD="align: right"]$47[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$1,077[/TD]
[TD="align: right"]$27[/TD]
[TD="align: right"]$15[/TD]
[TD] [/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]15727[/TD]
[TD]NJ[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2016:123456:V1[/TD]
[TD]ABC[/TD]
[TD]DEF[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$4,215[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$31,818[/TD]
[TD="align: right"]$27,603[/TD]
[TD]New York[/TD]
[TD]5326[/TD]
[TD="align: right"]$27[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$16[/TD]
[TD="align: right"]$20[/TD]
[TD="align: right"]$12[/TD]
[TD] [/TD]
[TD]X[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NJ[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2016:123456:V1[/TD]
[TD]ABC[/TD]
[TD]DEF[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$4,215[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$31,818[/TD]
[TD="align: right"]$27,603[/TD]
[TD]Ohio[/TD]
[TD]5907[/TD]
[TD="align: right"]$1,540[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$6,285[/TD]
[TD="align: right"]$7[/TD]
[TD="align: right"]$11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NJ[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2016:123456:V1[/TD]
[TD]ABC[/TD]
[TD]DEF[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$4,215[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$31,818[/TD]
[TD="align: right"]$27,603[/TD]
[TD]Locality 1[/TD]
[TD]5381[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$9[/TD]
[TD="align: right"]$28[/TD]
[TD="align: right"]$18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NJ[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2016:123456:V1[/TD]
[TD]ABC[/TD]
[TD]DEF[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$4,215[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$31,818[/TD]
[TD="align: right"]$27,603[/TD]
[TD]Locality 2[/TD]
[TD]5493[/TD]
[TD="align: right"]$1,262[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$1,278[/TD]
[TD="align: right"]$44[/TD]
[TD="align: right"]$11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NJ[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2016:123456:V1[/TD]
[TD]ABC[/TD]
[TD]DEF[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$4,215[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$31,818[/TD]
[TD="align: right"]$27,603[/TD]
[TD]Locality 3[/TD]
[TD]5334[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$1,120[/TD]
[TD="align: right"]$1,120[/TD]
[TD="align: right"]$24[/TD]
[TD="align: right"]$19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NJ[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2016:123456:V1[/TD]
[TD]ABC[/TD]
[TD]DEF[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$4,215[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$31,818[/TD]
[TD="align: right"]$27,603[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2016:123456:V1[/TD]
[TD]ABC[/TD]
[TD]DEF[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$4,215[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$31,818[/TD]
[TD="align: right"]$27,603[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]678947[/TD]
[TD]2016:678947:V1[/TD]
[TD]GHI[/TD]
[TD]JKL[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$3,587[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$32,418[/TD]
[TD="align: right"]$91,138[/TD]
[TD]Alabama[/TD]
[TD]5590[/TD]
[TD="align: right"]$60[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$412[/TD]
[TD="align: right"]$33[/TD]
[TD="align: right"]$14[/TD]
[TD] [/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]18857[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]678947[/TD]
[TD]2016:678947:V1[/TD]
[TD]GHI[/TD]
[TD]JKL[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$3,587[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$32,418[/TD]
[TD="align: right"]$91,138[/TD]
[TD]California[/TD]
[TD]5793[/TD]
[TD="align: right"]$27[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$1,997[/TD]
[TD="align: right"]$41[/TD]
[TD="align: right"]$17[/TD]
[TD] [/TD]
[TD]X[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]678947[/TD]
[TD]2016:678947:V1[/TD]
[TD]GHI[/TD]
[TD]JKL[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$3,587[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$32,418[/TD]
[TD="align: right"]$91,138[/TD]
[TD]Colorado[/TD]
[TD]5963[/TD]
[TD="align: right"]$1,937[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$64[/TD]
[TD="align: right"]$50[/TD]
[TD="align: right"]$12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]678947[/TD]
[TD]2016:678947:V1[/TD]
[TD]GHI[/TD]
[TD]JKL[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$3,587[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$32,418[/TD]
[TD="align: right"]$91,138[/TD]
[TD]Pennsylvania[/TD]
[TD]5490[/TD]
[TD="align: right"]$21[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$752[/TD]
[TD="align: right"]$39[/TD]
[TD="align: right"]$17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]678947[/TD]
[TD]2016:678947:V1[/TD]
[TD]GHI[/TD]
[TD]JKL[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$3,587[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$32,418[/TD]
[TD="align: right"]$91,138[/TD]
[TD]Maine[/TD]
[TD]5297[/TD]
[TD="align: right"]$1,348[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$504[/TD]
[TD="align: right"]$10[/TD]
[TD="align: right"]$20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]678947[/TD]
[TD]2016:678947:V1[/TD]
[TD]GHI[/TD]
[TD]JKL[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$3,587[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$32,418[/TD]
[TD="align: right"]$91,138[/TD]
[TD]Cincinnati[/TD]
[TD]5145[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$1,890[/TD]
[TD="align: right"]$88[/TD]
[TD="align: right"]$20[/TD]
[TD="align: right"]$10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]678947[/TD]
[TD]2016:678947:V1[/TD]
[TD]GHI[/TD]
[TD]JKL[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$3,587[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$32,418[/TD]
[TD="align: right"]$91,138[/TD]
[TD] [/TD]
[TD]5723[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]678947[/TD]
[TD]2016:678947:V1[/TD]
[TD]GHI[/TD]
[TD]JKL[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$3,587[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$32,418[/TD]
[TD="align: right"]$91,138[/TD]
[TD] [/TD]
[TD]5940[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CA[/TD]
[/TR]
</tbody>[/TABLE]

The result I need in Sheet 1 is as follows:
[TABLE="width: 9625"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="3"><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Unique ID[/TD]
[TD]Checking or Savings[/TD]
[TD]January 1, 2016 Balance[/TD]
[TD]December 31, 2016 Balance[/TD]
[TD]Foreign Account[/TD]
[TD]Foreign Account balance[/TD]
[TD]Penalty and Interest[/TD]
[TD]Penalty[/TD]
[TD]Interest[/TD]
[TD]Federal Balance Due / (Refund)[/TD]
[TD]Resident State Balance Due / (Refund)[/TD]
[TD]Resident Filing 2 Balance Due / (Refund)[/TD]
[TD]NR State 1 Balance Due / (Refund)[/TD]
[TD]NR State 2 Balance Due / (Refund)[/TD]
[TD]NR State 3 Balance Due / (Refund)[/TD]
[TD]NR State 4 Balance Due / (Refund)[/TD]
[TD]Resident State[/TD]
[TD]Credit card number[/TD]
[TD]State Interest Paid/ (State Interest received)[/TD]
[TD]NR State 1[/TD]
[TD]Credit card number[/TD]
[TD]State Interest Paid/ (State Interest received)[/TD]
[TD]NR State 2[/TD]
[TD]Credit card number[/TD]
[TD]State Interest Paid/ (State Interest received)[/TD]
[/TR]
[TR]
[TD="align: right"]123456[/TD]
[TD]Should be based on column T or US from result tab[/TD]
[TD]Column K result tab[/TD]
[TD]Column L, Result Tab[/TD]
[TD]Should mark as "X", if result tab, column V is marked as "X".[/TD]
[TD]Should pull the amounts from column W, Result tab, if Column E in this sheet and Column V in result tab is marked as "X"[/TD]
[TD]Total of column F2 and G2[/TD]
[TD]Total of sheet Result, column Q for corresponding UID[/TD]
[TD]Total of sheet Result, column R for corresponding UID[/TD]
[TD](Interest Received)/Interest Paid[/TD]
[TD]State Interest Paid/ (State Interest received) For New Jersey[/TD]
[TD]State Interest Paid/ (State Interest received) For New York[/TD]
[TD]State Interest Paid/ (State Interest received) For Ohio[/TD]
[TD]State Interest Paid/ (State Interest received) For Locality 1[/TD]
[TD]State Interest Paid/ (State Interest received) For Locality 2[/TD]
[TD]State Interest Paid/ (State Interest received) For Locality 3[/TD]
[TD]Column W, sheet Result[/TD]
[TD]Should be based on Column V, resident state, arrived from result tab, Column N using UID[/TD]
[TD]Should be based on Column V, resident state, arrived from result tab, Column O or P using UID[/TD]
[TD]Column M, sheet Result M3, based on UID[/TD]
[TD]Should be based on Column Y, NR state 1, arrived from result tab, Column N using UID[/TD]
[TD]Should be based on Column Y, NR state 1, arrived from result tab, Column O or P using UID[/TD]
[TD]Column M, sheet Result M4, based on UID[/TD]
[TD]Should be based on Column AB, NR state 2, arrived from result tab, Column N using UID[/TD]
[TD]Should be based on Column AB, NR state 2, arrived from result tab, Column O or P using UID[/TD]
[/TR]
</tbody>[/TABLE]

This is the same file that I posted in dropbox. I hope now either you or anyone else would be able to help me on this. Thanks a lot in advance.
 
Upvote 0
You must log in or register to reply here.

Similar threads

ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
390
ChrisMac1
ChrisMac1
H
  • Solved
Dynamically pulling allowances and ignoring certain allowance for each employee
2
Replies
13
Views
586
hagia_sofia
hagia_sofia
G
  • Question Question
Improve calculation speed for this worksheet with INDIRECT/FILTER functions
Replies
0
Views
375
GeneBF
G
L
  • Solved
Linking Data on Different Tab and Showing Blank Values Instead of Zeroes
Replies
3
Views
438
Fluff
Fluff
N
  • Question Question
Pivot graph data when creating a new tab from a template.
Replies
0
Views
248
Nippyzap
N

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top