Q: IIF Statement

[Edu07]

Hi,

Sheet 1 column A is alphanumeric, B is text and C is price, Sheet 2 has the
same condition. In sheet 3 I'd like to check Sheet1 ColA and Sheet2 ColA if
is equal then Store alphanumeric data in Sheet3 ColA and Sum Price (sheet1
colC + sheet2 colC). Pls note that there're over 1000 entries in each sheet
and data in sheet1 ColA Row 10 is not necessarily equal to sheet2 ColA Row10
but may it be in Row150.

How can it be done using IIF Statement?

TIA

Edu

 

[Max]

My thoughts would be to copy n paste Sheet2's data below Sheet1's (assumed
identically structured), then pivot on the combined source, placing col A's
header into ROW area, "Price" into DATA area (SUM). Just a couple of seconds
worth of effort, and it should yield the desired results.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik

 

[Emilio]

Max,
I don’t think it would work because as he said, sheet1 ColA and sheet2 ColA
have the same data but not necessarily located in the same row. Sheet3
should scan Sheet1 ColA and sheet2 ColA for a match and if exists storing it
in sheet3 summing ColC of the match. I’m not sure Excel can do this.
Cheers,
Emilio

 

[Max]

I'm not sure. Best to have the OP's feedback to the thoughts. Anyway,
assuming your angle, it's always possible to do a backtest using the pivot
o/p.

Assuming the earlier pivot is now drawn from a combined source in a new
sheet, and the data for col A's uniques appears in A5 down, sums in B5 down
This backtest could be placed in C5:
=IF(AND(COUNTIF(Sheet1!A:A,A5),COUNTIF(Sheet2!A:A,A5)),B5,"")
with C5 copied down. An autofilter on col C could then be applied, and "(Non
blanks)" chosen to derive the result
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik