Nested IF statements going wrong

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
I have a spreadsheet of data I would like to reorganise in a certain way. The current format is:

Client ID Client Name Employee Name Role Start Date
1234 Bunny Plc Cox, B Account Director 14/04/2002
1234 Bunny Plc Hollingworth, D Account Administrator 28/09/1999
1234 Bunny Plc West, F Account Manager 12/05/2010
1235 Eggs Plc Blunt, S Account Administrator 12/04/2002
1235 Eggs Plc Krane, S Account Director 01/10/1999
1235 Eggs Plc Miller, P Account Manager 06/10/1999
1236 Neverland Limited Kelly, M Account Administrator 30/09/1999
1236 Neverland Limited Lewis, G Account Director 03/10/1999
1236 Neverland Limited Smith, K Account Manager 14/07/2006
1237 Hello Mobile Plc French, S Account Manager 17/04/2002
1237 Hello Mobile Plc Lister, Q Account Administrator 05/10/1999
1237 Hello Mobile Plc Shelley, C Account Director 13/07/2006
1238 Big Burger Plc Christian, L Account Director 13/04/2002
1238 Big Burger Plc Parsons, N Account Manager 11/07/2006
1238 Big Burger Plc Ying, Y Account Administrator 14/05/2010
1239 Fruity Fruit Limited Eagle, J Account Manager 16/04/2002
1239 Fruity Fruit Limited Fuchs, C Account Director 18/04/2002
1239 Fruity Fruit Limited Lennox, A Account Administrator 02/10/1999
1240 Fresh Bread Limited Crud, T Account Administrator 15/04/2002
1240 Fresh Bread Limited Hart, C Account Manager 27/09/1999
1240 Fresh Bread Limited Lim, Y Account Director 04/10/1999
1241 Zuma Fashion Limited Aston, B Account Manager 09/04/2002
1241 Zuma Fashion Limited Bellingham, K Account Director 10/04/2002
1241 Zuma Fashion Limited Ramirez, J Account Administrator 12/07/2006
1242 Forever Plc Blair, D Account Director 11/04/2002
1242 Forever Plc Kellog, P Account Manager 29/09/1999
1242 Forever Plc Williams, R Account Administrator 13/05/2010

But I want it displayed as follows:

Client ID Client Name Account Director Start Date Account Manager Start Date Account Administrator Start Date
1234 Bunny Plc Cox, B 14/04/2002 West, F 12/05/2010 Hollingworth, D 28/09/1999
1235 Eggs Plc Krane, S 01/10/1999 Miller, P 06/10/1999 Blunt, S 12/04/2002
1236 Neverland Limited Lewis, G 03/10/1999 Smith, K 14/07/2006 Kelly, M 30/09/1999
1237 Hello Mobile Plc Shelley, C 13/07/2006 French, S 17/04/2002 Lister, Q 05/10/1999
1238 Big Burger Plc Christian, L 13/04/2002 Parsons, N 11/07/2006 Ying, Y 14/05/2010
1239 Fruity Fruit Limited Fuchs, C 18/04/2002 Eagle, J 16/04/2002 Lennox, A 02/10/1999
1240 Fresh Bread Limited Lim, Y 04/10/1999 Hart, C 27/09/1999 Crud, T 15/04/2002
1241 Zuma Fashion Limited Bellingham, K 10/04/2002 Aston, B 09/04/2002 Ramirez, J 12/07/2006
1242 Forever Plc Blair, D 11/04/2002 Kellog, P 29/09/1999 Williams, R 13/05/2010

I have resolved the issue of the first two columns by applying an Advanced Filter and copying it over to a different sheet, and successfully converted the other fields using a nested IF statement:

=IF(A1=sheet2!A1:A28,IF(sheet2!d1:d28=C1,sheet2!C1:C28,""),"")

The problem is that it worked in my test sheet, where i reduced the amount of rows to about 256, but not in my master sheet, where the data source has 11077 rows and 5 columns and my sheet with the formulas etc has 8122 lines and 30 columns.

I thought that Excel was simply incapable of dealing with large amounts of data, but I broke up the data source to reduce the number of lines being checked (from 11077 to 136) but that still didn't work. In one of the fields only some of the values are returned even though I know there are values for the others too. I also get the #VALUE error appearing in some of the cells, which doesn't make sense as it should come up like that for all if there was an error.

Has Excel gone insane or am I just being an idiot?

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
There is limitation of 7 nested ifs.

I was not able to understand your data. Could you please upload a sample file with sample data etc on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.